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

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.

ms189569.note(en-US,SQL.90).gifNote:
The bcp command and BULK INSERT statement allow 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.

The following example exports the names from the AdventureWorks Person.Contact 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 AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout Contacts.txt -c -T 
ms189569.security(en-US,SQL.90).gifSecurity 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.

Release History

17 July 2006

Changed content:
  • Added a security note to the "Examples" section.
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.