How can I produce multiple output flat files from a single SQL filewith multiple SQL statements?
Question posted by: Tim
(Guest)
on
August 22nd, 2008 11:15 AM
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
3
Answers Posted
On Aug 22, 6:14*am, Tim <tim_roger...@hotmail.comwrote:
Quote:
Originally Posted by
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.comwrote:
Quote:
Originally Posted by
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.
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,049 network members.
Top Community Contributors
|
