Tip: Beware of Using the QUERYOUT Option with Command-Line Bcp

Bulk copy program (bcp) lets you copy data out of SQL Server based on the result set of a query that's specified on the bcp command line. The Q&A "Does the SQL Server 7.0 version of bulk copy program (bcp) let you create an output file from a query without having a VIEW to put the result set in?" August 1999, InstantDoc ID 5867, explains how you can use the QUERYOUT option to accomplish this kind of data copying. But a SQL Server Magazine reader recently told me about a bug in bcp that forces the query specified in QUERYOUT to execute twice. (For a full description of this bug, see the Microsoft article "BUG: Bcp.exe Executes Query Twice When QueryOut Argument Used" at http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;q309555.)

Of course, executing a query twice is inefficient, especially when you're dealing with large data sets. You can still selectively bcp out data by defining a view in SQL Server that encapsulates the query you'd otherwise specify with the QUERYOUT option. You'll get the same output data set, but the query won't run twice.