I have several queries that need to be run on a weekly basis in Microsoft SQL Server Management Studio, each one one is just a relatively simple select query, and the results need to be saved into csv file. Right now someone spends an hour running each script in turn and saving the results.
I figured this could be somewhat automated but am struggling.
From reading previous questions here I've gotten as far as using SQLCMD mode, and by putting :output c:\filename.csv I get the output saved into a file, but I am having trouble getting separate files to be generated for each query.
For simplicity's sake, assume my query looks like this:
OUT: C:\File1.csv
SELECT * FROM table1;
OUT: C:\File2.csv
SELECT * FROM table2;
OUT: C:\File3.csv
SELECT * FROM table3;
Instead of getting three files with the output of each query, I end up with File1 and File2 filled with a couple of unreadable characters, and all three queries in File3. I know in Oracle there is a spool off command, is there something similar for OUT: in SSMS?
I ran a somewhat modified query and was able to get three files with three different query results. I ran the following for a quick test:
:OUT C:\File1.csv
SELECT 'Hello'
GO
:OUT C:\File2.csv
SELECT 'My'
GO
:OUT C:\File3.csv
SELECT 'Friend'
This gave me three separate files with the results from each query in a separate file. All I did was take out the semi colon and added the keyword GO which will terminate a command and move on to the next one. I hope this helps.