SQL Server 2008 Books Online (October 2009)
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 INSERT Transact-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.

ms187086.note(en-us,SQL.100).gifImportant:
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.

Example

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;
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.100).gifNote:
For an example of using a view in a BULK INSERT command, see Using a Format File to Skip a Table Column.

See Also

Concepts

Scenarios for Bulk Importing and Exporting Data

Other Resources

Adding Rows by Using Bulk Copy Operations
bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Modifying Data Through a View

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Page view tracker