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

[Plamen Ratchev]

Re: How can I produce multiple output flat files from a single SQLfile with multiple SQL statements?

One way is to use the BCP utility:

http://technet.microsoft.com/en-us/library/ms162802.aspx

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell

--
Plamen Ratchev

http://www.SQLStudio.com

[nidaar]

Re: How can I produce multiple output flat files from a single SQLfile with multiple SQL statements?

On Aug 22, 6:14 am, Tim <tim_roger...@h otmail.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.

[nidaar]

Re: How can I produce multiple output flat files from a single SQLfile with multiple SQL statements?

On Aug 22, 6:14 am, Tim <tim_roger...@h otmail.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.