Generating query files named based on contents of table

Question

Generating query files named based on contents of table

SQLUserC

SSCrazy

Points: 2345
More actions
April 14, 2011 at 3:56 pm

#249450

Hey all,
I am trying to design a table to manage replication ranges between servers. I would like to use this table to automatically generate seed queries based on the contents of the table. Things are coming along nicely, but one thing I'm not clear on is how to save the results of a query that writes the seed queries as separate files based on the server names (which are contained in the table).
I guess I'm asking a fairly simple question--what statements in T-SQL can be used to save the results of a query to a query file. How can I get the name of the file generated to be dependent on the contents of a field? I would appreciate any help anyone can give. Please let me know if you have any questions.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Using only T-SQL? Unfortunately there is no BULK EXPORT command to counter the BULK INSERT command. You're options are to call bcp using xp_cmdshell (yuck!) or move the process into PowerShell to (1) select the metadata you need to know to generate the file and (2) call bcp to export the data into a text file with the SELECT query and file name you want.

Once you give up on doing everything from T-SQL you have many more options too: SSIS, .NET, etc. etc.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLUserC SSCrazy Points: 2345 More actions · Answer 2

Hey all,

A colleague of mine recommended a good solution to this issue, so I thought I'd share it here.

You can use osql with the -o switch. This will output the results of the query from the file.

So, for example:

osql -S [servername] -E -Q "select 'select * from [table1]'" -o "C:\[OutputDir]"

Note that anything I included between square brackets is not meant to be typed literally, but to substitute for a relevant item that you supply. Let me know if there are any questions.

Thanks.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

That will certainly work. If you're on 2008 you'll want to use sqlcmd which should also let you do the same thing. osql is a SQL 2000 tool and AFAIK it's being phased out in favor of sqlcmd.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato