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

One way is to use the BCP utility:
http://technet.microsoft.com/en-us/l.../ms162802.aspx
http://www.sqlteam.com/article/expor...nd-xp_cmdshell

--
Plamen Ratchev
http://www.SQLStudio.com

On Aug 22, 6:14*am, Tim <tim_roger...@hotmail.com> wrote:
> 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.

On Aug 22, 6:14*am, Tim <tim_roger...@hotmail.com> wrote:
> 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.