Dynamic select output to text file ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic select output to text file ?

Author

Topic

mberggren
Starting Member

15 Posts

Posted - 2011-08-16 : 05:31:07

Hi,

I´m trying to make my SQL statement more dynamic and efficient.
In my table I have a unique autoincrement number

ObNum

.
The total unique rows

quote:
ObNum

could vairy a lot from day to day. So for every time a new unique row is added I have to change the code of my SQL.

To get a result from a row the SQL looks like this.
Example:


SELECT my_table.ObNum,

my_table.column1, // 2
my_table.column2, // 3
my_table.column3, // 4

FROM my_table

WHERE date = today() - 1 AND  quote:
ObNum = 1
;
OUTPUT TO c:\export\ quote:
rest_01.txt
 delimited by '\x09'

Next row...
Example:


SELECT my_table.ObNum,

my_table.column1, // 2
my_table.column2, // 3
my_table.column3, // 4

FROM my_table

WHERE date = today() - 1 AND  quote:
ObNum = 2
;
OUTPUT TO c:\export\ quote:
rest_02.txt
 delimited by '\x09'

So for every output file I have a SQL stament.

Anyone with a simple solution to make this more dynamic ?

// Regards

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-08-16 : 17:19:05

First this is a MS SQL server site... BUT I would use something like Oracles EXECUTE IMMEDIATELY or something like it to execute a string as a query and execute it in a cursor or a while loop. Just build the string to execute with variables for file name and obnum.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:11:40

you can use bcp with query out option.

see
http://www.mssqlcity.com/FAQ/Devel/TbToFile.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources