Write T-SQL Result Lines to Individual Files

Question

I am working on a proprietary data warehousing product that has a metadata table containing the SQL queries used to populate it. I need to extract each of these queries from the metadata table and save it to its own .sql file, named according to the table it populates.

There are about 400 of these queries, and I'd like to automate it, but I don't know a good way to do that. Is there a good way to iterate across the result set, line-by-line, and write the output to a file?

An example of what I'm trying to do. Let's say I returned two lines in my result set:

select tableName, extractQuery from packages;

tableName extractQuery

Table1 select * from sourceTable1;

Table2 select * from sourceTable2;

Then I would need this to be saved into two distinct files, Table1.sql and Table2.sql, each containing the returned SQL command.

Are you trying to do all of this in SQL within SQL Server Management Studio, or can you write a script (cmd or powershell) or C# command line utility or something? What's your environment? — pmbAustin, Apr 25 '17 at 20:30
You may also want to consider using an SSIS package and a script component. — Andrew O'Brien, Apr 25 '17 at 20:43

KeithL · Answer 1 · 2017-04-26T15:28:58.160

To expand on @Andrew O'Brien 's SSIS and script suggestion:

Step 1: Create a SSIS package.

Step 2: Add a data flow

Step 3: Add your SQL Source

Step 4: Add a Script Component

Inside SQL Component:

Select Transformation

check your 2 columns as inputs

edit script: paste this in there:

    //This is the path to where you want the results stored
    string filePath = @"\\server\path\"; 
    string fileName = Row.tableName.ToString() + ".sql";

    System.IO.File.WriteAllText(Row.extractQuery.ToString(),filePath+fileName);

This should write out every file.

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

You can do this with a BCP command and a cursor.

1) Make sure you create the destination folder before running

2) Make sure that command line utilities are enabled

3) Make sure the SqlService account has access to the folder

declare c cursor for
select tableName from packages

declare @cvar varchar(64)

open c
fetch next from c into @cvar

while @@FETCH_STATUS = 0
begin


DECLARE @OutputFile NVARCHAR(100) ,    @FilePath NVARCHAR(100) ,    @bcpCommand NVARCHAR(1000)

SET @bcpCommand = 'bcp "SELECT extractQuery FROM yourServer.dbo.packages where tableName = '''+ @cvar + '''" queryout '
SET @FilePath = 'C:\test\'
SET @OutputFile = @cvar + '.txt'
SET @bcpCommand = @bcpCommand + @FilePath + @OutputFile + ' -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @bcpCommand

fetch next from c into @cvar

end
close c
deallocate c

Write T-SQL Result Lines to Individual Files

2 Answers2