Export (0) Print
Expand All
2 out of 3 rated this helpful - Rate this topic

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.