By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,302 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,302 IT Pros & Developers. It's quick & easy.

Writing multiple queries to a file with BCP

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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


Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.