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.

Examples

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 

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.

See Also

Concepts

Controlling the Sort Order When Bulk Importing Data
Scenarios for Bulk Importing and Exporting Data

Other Resources

bcp Utility
BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History