Exporting Data from a Query to a Data File
The bcp command allows you to export the result set from a Transact-SQL statement to a data file. The Transact-SQL statement can be any valid statement that returns a results set, such as a distributed query or a SELECT statement joining several tables.
Bulk exporting data from a table or view does not guarantee the order in which the data is written to the data file. You can use a query to ensure that a bulk-export operation preserves the order of the table data in the data file.
Note that some Transact-SQL statements returns multiple result sets; for example, a SELECT statement that specifies the COMPUTE clause or a stored procedure that contains multiple SELECT statements. If the Transact-SQL statement returns multiple result sets, only the first result set is copied; subsequent result sets are ignored.
Note
The bcp command, BULK INSERT statement, and OPENROWSET (BULK…) function (Transact-SQL) enable you to specify how data in the data file is sorted during a bulk-import operation. For more information, see Controlling the Sort Order When Bulk Importing Data. Preserving the data order during a bulk import can significantly improve performance. For more information, see Optimizing Bulk Import Performance.
Examples
The following example exports the names from the AdventureWorks2008R2 Person.Person table into the Contacts.txt data file. The names are ordered by last name then first name.
The example is executed from the Microsoft Windows command prompt:
bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
.gif "Security note") Security Note |
|---|
You might have to specify the name of the server instance to which you are connecting. Also, you might have to specify the user name and password. For more information, see bcp Utility. |