Posted by Erland Sommarskog on 05/27/05 00:14

caro (caroh@orange.net) writes:
> I am trying to write two Select * statements to the same text file
> using bcp (from a stored procedure).
>
> But cannot find a way of appending to a file using bcp.
>
> Does anyone know if this is possible or is there another way of writing
> multiple queries to a file from a stored procedure?

If the queries have the same structure on their result sets, you could
combine then with UNION:

SELECT ....
UNION ALL
SELECT ...

However, assuming that you want all the rows from the first query to
come before all rows in the second query, you need an ORDER BY clause
to handle this. The standard trick is to add an extra column:

SELECT queryno = 1, ....
UNION ALL
SELECT 2, ...
ORDER BY queryno

But this would mean in your case that you get an extra column in the
file. You could circumvent this by creating a temp table with an
IDENTITY column, and then SELECT from this temp table ordering by
this IDENTITY column, without including that column itself.

A much simpler solution could be to bulk copy to separate files,
and then use the COPY command to combine the files into one. Do
COPY /? at the command prompt for more information.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]