How do I create a temporary table for a dynamic query so that I can BCP the output

I am trying to write a stored procedure that executes all .sql files that are in a folder, and then outputs the results to CSV files.

My code:

DECLARE @Table table(
    [FileName] varchar(100),
    depth int,
    isFile int
)

DECLARE @Result int
DECLARE @FileName VARCHAR(100)
DECLARE @exportFile VARCHAR(100)
DECLARE @ExportPath VARCHAR(100)
DECLARE @SQLText VARCHAR(MAX)
DECLARE @FilePath VARCHAR(100)
DECLARE @FULLPATH VARCHAR(200)
DECLARE @BULKEXEC NVARCHAR(MAX)
DECLARE @Cmd nvarchar(1000)

SET @FilePath = 'c:\temp'
SET @ExportPath = 'c:\temp\output'

INSERT INTO @Table
EXEC master..xp_dirtree @FilePath,1,1

DELETE FROM @Table WHERE isFile=0

DECLARE FileList CURSOR STATIC FOR SELECT [FileName] FROM @Table WHERE FileName LIKE '%.sql'
OPEN FileList
FETCH FIRST FROM FileList INTO @FileName
    WHILE @@Fetch_Status = 0
        BEGIN
            SET @exportFile = REPLACE(@FileName,'.sql','.csv')
            SET @FULLPATH = @FilePath + '\' + @FileName
            SET @BULKEXEC = 'SELECT @SQLText = BulkColumn FROM OPENROWSET(BULK ''' + @FULLPATH + ''', SINGLE_BLOB) as x'  

            EXEC sp_ExecuteSQL @BULKEXEC, N'@SQLText VARCHAR(MAX) output ', @SQLText output

            EXEC (@SQLText)



            SET @Cmd = 'bcp "SELECT * FROM ##Temptable" queryout "' + @ExportPath + '\' + @exportFile + '" -c -t, -T'

            EXEC @Result = master..xp_cmdshell @cmd

            FETCH NEXT FROM FileList INTO @FileName
        END
CLOSE FileList
DEALLOCATE FileList

This currently executes the files, but I'm missing the middle bit that uploads the results to the ##Temptable.

Is anyone able to help?

Solution

Take a look at the following example. It uses a query in @sqltext, a quite generic query that selects information from the INFORMATION_SCHEMA schema (tables and columns).

This query is then modified to insert the results into a global temporary table ##tt, by looking for the first FROM keyword and inserting INTO ##tt at the proper location. The modified query is executed to insert the results of the query in the temporary table.

Finally the results are written to a text file using the xp_cmdshell procedure with a BCP command.

DECLARE @i INT=1;
WHILE @i<10
BEGIN
    DECLARE @sqltext NVARCHAR(MAX);
    IF @i%2=1 
        SET @sqltext='SELECT*FROM INFORMATION_SCHEMA.tables';
    ELSE
        SET @sqltext='SELECT*FROM INFORMATION_SCHEMA.columns';

    DECLARE @ii INT; SET @ii=CHARINDEX('FROM',@sqltext);

    SET @sqltext=LEFT(@sqltext,@ii-1)+' INTO ##tt '+SUBSTRING(@sqltext,@ii,LEN(@sqltext));
    EXEC(@sqltext);

    DECLARE @bcp_cmd NVARCHAR(4000);
    SET @bcp_cmd='BCP "SELECT*FROM ##tt" QUERYOUT "c:\temp\tt'+CAST(@i AS NVARCHAR)+'.txt" -c -t, -T -d ' + DB_NAME() + ' -S '+ @@SERVERNAME;
    EXEC xp_cmdshell @bcp_cmd;

    DROP TABLE ##tt;
    SET @i=@i+1;
END

If it is not possible to format the queries in the .sql files because they are complex scripts, the sqlcmd utility will be a lot easier to use. You can specify an input file (your SQL script) with -i and an output file (the CSV files) with -o. -s for the columns separator and so on.