How to export multiple select statements to different csv files in SQL Server query

Question

I have a long SQL server query, which runs on different tables, does some pre-processing and spits out different select table outputs. I want them to be stored in different csv files.

simplified eg.

-- some declare variables and other pre-processing,
-- then save the outputs to different CSV files
select * from Table1 -- save to Table1_Output.csv
select * from Table2 -- save to Table2_Output.csv

Now, I can use this to run SQLCmd and save the output for a single select statement to a single CSV. I need to be able to save the various outputs to different CSVs

The first option in the solution above is not viable as that requires manual intervention and cannot be used in a scripting environment.

Thanks

Bacon Bits · Accepted Answer · 2016-01-21T14:45:45.447

5

Personally, I'd just use PowerShell if you can't use SSIS:

$SQLServer = 'SQLSERVERNAME';
# If you have a non-default instance such as with SQL Express, use this format
# $SQLServer = 'SQLSERVERNAME\SQLINSTANCENAME';
$Database = 'MyDatabase';

$ConnectionString = "Data Source={0};Initial Catalog={1};Integrated Security=SSPI" -f $SQLServer, $Database;
$CommandText = @'
DECLARE @ID int;
SET @ID  = 123456;
SELECT ID, First_Name FROM dbo.Person WHERE ID = @ID;
SELECT ID, Last_Name FROM dbo.Person WHERE ID = @ID;
'@;

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = $ConnectionString;

$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = $CommandText;
$SqlCommand.Connection = $SqlConnection;

$SqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlDataAdapter.SelectCommand = $SqlCommand;

$DataSet = New-Object System.Data.DataSet;

$SqlConnection.Open();
$SqlDataAdapter.Fill($DataSet) | Out-Null;
$SqlConnection.Close();
$SqlConnection.Dispose();

$DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path 'U:\FirstNames.csv';
$DataSet.Tables[1] | Export-Csv -NoTypeInformation -Path 'U:\LastNames.csv';

edited Jan 21 '16 at 14:45

answered Jan 20 '16 at 15:56

Bacon Bits

30,782
5
59
66

I ran this script (defining $SQLServer & $Database before that) and gave the error `Cannot index into a null array` at `$SqlDataAdapter.Tables[0] ...`. Can you give some context please? I have two queries in the $CommandText that run OK in SSMS. What parameters do I need to pass? – TT. Jan 20 '16 at 18:42
@TT. I would only expect that if your queries weren't returning any result sets. `$SqlDataAdapter.Tables` should be an array with one DataTable in each element of the array. – Bacon Bits Jan 20 '16 at 21:55
They're simple selects (`SELECT*FROM table1` style) in tables that have 1000's of rows. But perhaps I'm passing the wrong parameters? Right now I'm running this with `$SQLServer="UPS-NB01\SQLEXPRESS"` (the return of `SELECT @@SERVERNAME` in SSMS) and `$Database="TEST_TT"` (a database that exists). Did you test this script yourself before posting? And with what parameters did you run it? Mind expanding your answer a little to make it useful to everyone? – TT. Jan 21 '16 at 07:25
@TT. Actually, I copied my script wrong. The last two lines were incorrectly referring to `$SqlDataAdapter.Tables`, which is a property that doesn't exist! They need to refer to **`$DataSet.Tables`**. My script that does this does a lot more manipulation, and I used the wrong variable stripping that out. Sorry about that! – Bacon Bits Jan 21 '16 at 14:40
Great =) That worked. I'm assuming these are .NET classes you are using and it's been a long time since I worked with .NET. Otherwise I would have tried to dig into the script to solve it myself. I probably still will, to first see how many `Tables` are present and iterate through them to do the export (for sports). Thanks for the correction & the added context! (the +1 I already gave before testing this script). – TT. Jan 21 '16 at 14:49
It was easy. For reference to other readers: I replaced last two lines with `$Counter=1; ForEach($Table in $DataSet.Tables){ $Filename=".\Table{0}_Output.csv" -f $Counter; $Table | Export-Csv -NoTypeInformation -Path $Filename; $Counter=$Counter+1; }` – TT. Jan 21 '16 at 15:06
@TT. Yep, they're just the .NET classes. Don't forget to accept the answer if you've found one. – Bacon Bits Jan 21 '16 at 19:02
I would accept if it were me that asked the question ;) – TT. Jan 21 '16 at 19:03

score 2 · Answer 2 · edited May 23 '17 at 10:30

When multiple results are returned, sqlcmd prints a blank line between each result set in a batch. The output can then be processed with sed '/^$/q' or csplit -s - '/^$/' '{*}' to split into individual files: How to split file on first empty line in a portable way in shell.

UPD If sygwin is not an option custom VBScript can run sqlcmd through WScript.CreateObject("WScript.Shell") .Exec and process output line-by-line with .StdOut.ReadLine() until .StdOut.AtEndOfStream: Getting command line output in VBScript (without writing to files)

score 1 · Answer 3 · answered Jan 20 '16 at 15:22

An alternative approach is to use SSIS. SQL Server Integration Services is great tool for moving data in, around and out of databases. It's also easy to schedule SSIS packages, using the SQL Agent, should you wish to automate.

This would require a little more effort up front. You would need to break your existing batch into several smaller SQL statements.

How to export multiple select statements to different csv files in SQL Server query

3 Answers3