SQL Server Output multiple CSV files from one query

Question

I am trying to get SQL server to create multiple CSV files from one query. At this time we have 7 separate long running (2+ hours each) queries that need to be output to separate CSV files. I can create one query that calls all of them but that generates one giant CSV. Is there a way to tell SQL Server to create a separate CSV after each internal query has completed?

This question becomes more important as our next production run will have 52 of those long running queries and my boss does not want to have to run each of them separately.

EXEC dbo.Get_Result_Set1;
EXEC dbo.Get_Result_Set2;
EXEC dbo.Get_Result_Set3;
EXEC dbo.Get_Result_Set4;
EXEC dbo.Get_Result_Set5;
EXEC dbo.Get_Result_Set6;
EXEC dbo.Get_Result_Set7;

Damien_The_Unbeliever · Accepted Answer · 2017-02-07 14:41:06Z

2

You may want to create an SSIS package as the wrapper around executing these stored procedures, rather than your current query.

Each stored procedure can then be linked to a flat-file output.

This has the advantage that you should be able to express any required dependencies between the different invocations and (if possible) exploit some parallelism (if there are no dependencies between some/all of the invocations).

answered Feb 7, 2017 at 14:41

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

Comments

NathanAck · Accepted Answer · 2017-02-07 14:30:53Z

0

Could you create an Agent Job to do it? You could do a separate job step for each one of the queries. Under the advanced tab in the step section, there is an output option.

answered Feb 7, 2017 at 14:30

NathanAck

3513 silver badges9 bronze badges

3 Comments

Erin G Over a year ago

I did try to create a job but that did not seem to work. It never asked me for a file name and thought it seemed to run it did not produce any output. It is possible I configured something wrong in the job but everything looked correct.

NathanAck Over a year ago

It won't ask for an output. Much like the other answer suggesting to use SSIS, which honestly is probably a better idea, the output path would need to be hardcoded in the "Output File" box under advanced for the step.

Erin G Over a year ago

Thanks for that note about the output file. That makes sense. I am looking into the SSIS option right now.

Pang · Accepted Answer · 2017-02-15 00:57:33Z

Not the answer I was looking for but I wound up having someone help me write a C# procedure in Visual Studio that calls each of my SQL procedures and outputs each to an Excel file. It works and I can reuse it in the future.

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace StoredProcedureRunner
{
    class Program
    {
        public static void Main(string[] args)
        {
            var storedProcs = new List<string>();

            storedProcs.Add "dbo.Get_Result_Set1");
            storedProcs.Add "dbo.Get_Result_Set2");
            storedProcs.Add "dbo.Get_Result_Set3");
            storedProcs.Add "dbo.Get_Result_Set4");
            storedProcs.Add "dbo.Get_Result_Set5");
            storedProcs.Add "dbo.Get_Result_Set6");
            storedProcs.Add "dbo.Get_Result_Set7");

            foreach (var storedProc in storedProcs)
            {
                var table = GetDataTable(storedProc);
                WriteDataTableToCSV(storedProc + ".csv", table);
            }
        }

        public static DataTable GetDataTable(string storedProc)
        {
            DataTable table = new DataTable();

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStrg"].ConnectionString))
            {
                using (var command = new SqlCommand(storedProc, connection))
                {
                    using (var adapter = new SqlDataAdapter(command))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandTimeout = 0
                        adapter.Fill(table);
                    }
                }
            }

            return table;
        }

        public static void WriteDataTableToCSV(string filename, DataTable table)
        {
            StringBuilder sb = new StringBuilder();

            var columnNames = table.Columns.Cast<DataColumn>().Select(col => col.ColumnName);
            sb.AppendLine(string.Join(",", columnNames));

            foreach(DataRow row in table.Rows)
            {
                var fields = row.ItemArray.Select(field => field.ToString());
                sb.AppendLine(string.Join(",", fields));
            }

            File.WriteAllText(filename, sb.ToString());
        }
    }
}

Collectives™ on Stack Overflow

SQL Server Output multiple CSV files from one query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related