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

Usually you can bulk import data to a table view or bulk export data from a table view. These operations can include the export of data from multiple joined tables, the addition of a WHERE clause, or the performance of special formatting, such as a change of data formats by using the CONVERT function.

Bulk importing into a partitioned view is unsupported by BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...) statements, and attempts to bulk import data into a partitioned view fail.

The rules for inserting data into a view apply to bulk-importing of data into a view.

ms187086.note(en-US,SQL.90).gifImportant:
When data is bulk-imported into a view, the treatment of default values depends on the command. 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 Microsoft SQL Server Management Studio Query Editor, execute:

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

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;
GO

ms187086.note(en-US,SQL.90).gifNote:
For an example of using a view in a BULK INSERT command, see Using a Format File to Skip a Table Column.

Community Additions

ADD
Show:
© 2015 Microsoft