469,898 Members | 1,576 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

How can I produce multiple output flat files from a single SQL filewith multiple SQL statements?

Tim
Hi Folks,

I'm used to a UNLOAD command that allows me to dump to a named flat
file the results of any SELECT statement. Hence one can build a single
SQL file which contains multiple SQL statements each of which
'unloads' to its own unique file.

unload to 'file1.txt' select * from ......
unload to 'file2.txt' select * from ......
and so on....

I have a requirement to write a stop gap set of SQL to dump 10 flat
files from SQL server 2005.
I'd like to gather all these SQL statements into 1 or 2 SQL files so
Ops have an easy life running them and to remove as much possibility
for human error as possible.

However I can't do this if I cannot dump to individual flat files the
output of each SQL statement.

RTFM got me to the 'OUTPUT' command but that only seems to place the
data into a table not a flat file so I'm back at square one.

I assume there is a way round this of which I'm unaware, can someone
please point me in the right direction.

TIA, Tim
Aug 22 '08 #1
3 8277
On Aug 22, 6:14*am, Tim <tim_roger...@hotmail.comwrote:
Hi Folks,

I'm used to a UNLOAD command that allows me to dump to a named flat
file the results of any SELECT statement. Hence one can build a single
SQL file which contains multiple SQL statements each of which
'unloads' to its own unique file.

unload to 'file1.txt' select * from ......
unload to 'file2.txt' select * from ......
and so on....

I have a requirement to write a stop gap set of SQL to dump 10 flat
files from SQL server 2005.
I'd like to gather all these SQL statements into 1 or 2 *SQL files so
Ops have an easy life running them and to remove as much possibility
for human error as possible.

However I can't do this if I cannot dump to individual flat files the
output of each SQL statement.

RTFM got me to the 'OUTPUT' command but that only seems to place the
data into a table not a flat file so I'm back at square one.

I assume there is a way round this of which I'm unaware, can someone
please point me in the right direction.

TIA, Tim
Here is a fresh article about doing it using SSIS:
"How to Dynamically and Iteratively Populate An Excel Workbook from
SQL Server"
http://www.sqlservercentral.com/links/788627/60118

Also utilizing bcp (bulk copy program) in a batch can help (See bcp in
SQL Server Books Online).

If the names and locations of the Excel files are persistent, then
creating linked server against those files and using TSQL statements
to SELECT, INSERT, UPDATE, DELETE data can be more convenient.
Aug 22 '08 #3
On Aug 22, 6:14*am, Tim <tim_roger...@hotmail.comwrote:
Hi Folks,

I'm used to a UNLOAD command that allows me to dump to a named flat
file the results of any SELECT statement. Hence one can build a single
SQL file which contains multiple SQL statements each of which
'unloads' to its own unique file.

unload to 'file1.txt' select * from ......
unload to 'file2.txt' select * from ......
and so on....

I have a requirement to write a stop gap set of SQL to dump 10 flat
files from SQL server 2005.
I'd like to gather all these SQL statements into 1 or 2 *SQL files so
Ops have an easy life running them and to remove as much possibility
for human error as possible.

However I can't do this if I cannot dump to individual flat files the
output of each SQL statement.

RTFM got me to the 'OUTPUT' command but that only seems to place the
data into a table not a flat file so I'm back at square one.

I assume there is a way round this of which I'm unaware, can someone
please point me in the right direction.

TIA, Tim
As an alternative to bcp, here is a fresh article about doing it using
SSIS:
"How to Dynamically and Iteratively Populate An Excel Workbook from
SQL Server"
http://www.sqlservercentral.com/links/788627/60118

If the names and locations of the Excel files are persistent, then
creating linked server against those files and using TSQL statements
to SELECT, INSERT, UPDATE, DELETE data can be more convenient.

Aug 22 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Raj Kotaru | last post: by
22 posts views Thread by Matthew Louden | last post: by
reply views Thread by Sells, Fred | last post: by
15 posts views Thread by naveenkongati | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.