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-21 14:45:45Z

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, 2016 at 14:45

answered Jan 20, 2016 at 15:56

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

TT. Over a year ago

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?

Bacon Bits Over a year ago

@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.

TT. Over a year ago

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?

Bacon Bits Over a year ago

@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!

TT. Over a year ago

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).

|

Community · Accepted Answer · 2017-05-23 10:30:28Z

2

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)

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Jan 20, 2016 at 15:11

Y.B.

3,58618 silver badges24 bronze badges

Comments

David Rushton · Accepted Answer · 2016-01-20 15:22:40Z

1

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.

answered Jan 20, 2016 at 15:22

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related