Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Bulk Exporting Data from or Bulk Importing Data to a View

You can bulk export data from any view by using a bcp command.

You can bulk import data into a view as follows:

  • Nonpartitioned views

    You can bulk import data into a nonpartitioned view by using a bcp command, a BULK INSERT statement, or an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement.

  • Partitioned views

    Bulk importing into a partitioned view is unsupported. However, you can insert multiple rows into a partitioned view, without bulk optimizations, by using an INSERTTransact-SQL statement. 

All bulk-import operations meet the rules for inserting data into a view. For information about those rules, see Modifying Data Through a View.

Important noteImportant

The treatment of default values by any bulk-import operation depends on which bulk-import command or statement was used. For more information, see Keeping Nulls or Using Default Values During Bulk Import.

The following example uses a view of the HumanResources.DepartmentView table of the AdventureWorks sample database. From a query tool such as SQL Server Management Studio Query Editor, execute:

CREATE VIEW DepartmentView AS 
    SELECT DepartmentID, Name, GroupName 
    FROM HumanResources.Department;

The following command bulk exports the data from the DepartmentView view into the DepartmentView.txt data file. At the Microsoft Windows command prompt, enter:

bcp AdventureWorks..DepartmentView out DepartmentView.txt -c -T

To delete this sample view, execute the following Transact-SQL statement:

DROP VIEW DepartmentView;


For an example of using a view in a BULK INSERT command, see Using a Format File to Skip a Table Column.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft