The bcp utility allows you to copy the results 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. For example, to copy the names of all the authors, ordered by surname, from the authors table in the pubs database to the Authors.txt data file, execute at the command prompt:
bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword
Bulk copying data from a query is useful if you want to ensure that the order of the data is preserved in the data file; bulk copying data from a table or view does not guarantee the order of the data written to the data file. Preserving the order of the data in the data file allows you to make use of the ORDER hint when bulk copying data from the data file back into a table. Using the ORDER hint can significantly improve bulk copy performance. For more information, see Optimizing Bulk Copy Performance.
If the Transact-SQL statement returns multiple result sets, such as a SELECT statement that specifies the COMPUTE clause, or the execution of a stored procedure that contains multiple SELECT statements, only the first result set is copied; subsequent result sets are ignored.
| bcp Utility | Ordered Data Files |