Writing multiple queries to a file with BCP

[caro]

Hi

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?

Thanks
Caro

[Erland Sommarskog]

caro (ca***@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, es****@sommarskog.se

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

[caro]

Hi

Thanks for the suggestions! I think I will just copy the files together
(as origninally planned - since thats really straight forward.

Thanks again

Caro

Erland Sommarskog wrote:

caro (ca***@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, es****@sommarskog.se

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