Generate INSERT scripts from SQL Server queries and stored procedure output

By: Jeffrey Yao | Updated: 2016-05-13 | Comments (2) | Related: More > Database Administration

Problem

There are multiple ways via SQL Server Management Studio (SSMS) or dynamic SQL that we can generate a SELECT script that includes both the column name and the column value. This is useful to port data to other databases or to just have the data in a script that can be used to repopulate a table as needed. However, SSMS can only generate a select script on a table, but not on a query. While dynamic SQL can be used to do this for a query, it is not as easy as it sounds.

Let's say I have a query like the following:

SELECT ID, Name=FirstName + ' ' + LastName, AGE
FROM dbo.Staff
WHERE ID < 4
GO

This query returns the following result:

ID	Name	Age
1	John Doe	32
2	Jane Doe	40
3	Mary Smith	23

What I need to do is to create a script from this query (or stored procedure) to be like the following, how can I do this?

SELECT ID=1, Name='John Doe', Age=32
union all
SELECT ID=2, Name='Jane Doe', Age=40
union all
SELECT ID=3, Name='Mary Smith', Age=23

Solution

In theory, given a query we can create a pure T-SQL solution with the following design:

Using the query to dump the data into a temporary table, something similar to select ... into [temp_table] or insert into [temp_table] exec [stored proc]
Parsing this [temp_table] to get the column names and their data types and together with the table values to construct the expected SELECT query

This is easy when you have a simple query, but the problem with this pure T-SQL solution is that to create the [temp_table] it becomes very difficult to do when you do not know what data set you will get from a stored procedure (assuming depending on parameter values the stored procedure may return a different data set with different columns).

Even for a simple example, say I want to script out the results of "sp_who2 active", it will be time-consuming to do with a T-SQL solution since the sp_who2 result has two identical columns.

So I will use PowerShell, with SMO to come up with a more robust and generic solution.

Use SMO Database.ExecuteWithResuts method to execute one or multiple queries and get a DataSet object
Look through each DataTable in the DataSet.Tables
In each DataTable, loop through each DataRow
For each row, loop through each DataColumn, and based on the column data type, process the column value accordingly

This solution is wrapped as a PowerShell function, and as such, I can easily embed it into lots of my DBA automation work. For example, I can generate the script, write it into a .sql file, zip it and then send it to my recipient.

<#
.Synopsis
   Convert a database query result set to t-sql select script
.DESCRIPTION
   Convert a database query (or stored procedure) result, which may contain multiple data sets, into a sql select script.
.EXAMPLE
   
   Convert-QueryDatatToSQL -ServerInstance MyServer -Database MyDB -Query "select column_1, column_2 from dbo.MyTable where Column_3 > 10"

.EXAMPLE
   Convert-QueryDatatToSQL -ServerInstance MyServer -Database MyDB -Query "exec sp_who2 active"; 
.INPUTS
   -ServerInstance <string>
    The SQL Server instance name, which the query will connect to

   -Database [<string>]
    The target SQL Server database, against which the query will run
   
   -Query 
    simple select statements, or stored procedures that will return table-like result set
.OUTPUTS
   a string which is a select statement with union all to other select statements
.NOTES
   Author: Jeffrey Yao, 2016/04/20   
#>


#requires -version 4.0

# assume you have installed SQL Server 2012 (or above) Feature Pack, mainly the Shared Management Object file
#for sql 2012 version=11.0.0.0, for sql2014, version=12.0.0.0

add-type -AssemblyName "microsoft.sqlserver.smo, version=11.0.0.0, culture=neutral, PublicKeyToken=89845dcd8080cc91" -ea stop

Function Convert-QueryDataToSQL
{
    [CmdletBinding()]
    
    [OutputType([String])]
    Param
    (
        # SQL Server instance name, default to local server
        [Parameter(Mandatory=$true, 
                   Position=0)]
        [Alias("S")] 
        [string]$ServerInstance=$env:ComputerName,

        # Database Name
        [Parameter(Mandatory=$false,
                   ValueFromPipeline=$true,
                   Position=1)]
        
        [AllowEmptyCollection()]
        [string] $Database='master',

        # Query
        [Parameter(Mandatory=$true,
                   Position=2)]
        [String] $Query
    )
    
    [string[]]$columns='';
    [string] $sqlcmd = '';
    [string] $ret_value= '';

    try {
            $svr = new-object microsoft.sqlserver.management.smo.Server $ServerInstance;
            if ($svr -eq $null)
            {
                Write-Error "Server [$ServerInstance] cannot be accessed, please check";
                return -1;
            }
            $db = $svr.Databases.item($Database);
            if ($db -eq $null)
            {
                Write-Error "Database [$Database] cannot be accessed or does not exist, please check";
                return -1;
            }
    
            $result = $db.ExecuteWithResults($Query);
            if ($result.Tables.count -gt 0) # we now start to generate the strings
            {
                foreach ($t in $result.tables) #loop through each DataTable
                {
                      Foreach ($r in $t.Rows) #loop through each DataRow
                      {
                          $sqlcmd = 'select ' 
                          Foreach ($c in $t.Columns) #loop through each DataColumn
                          {
                            if ($r.item($c) -is 'DBNULL')
                            { $itm = 'NULL';}
                            else
                            { 
                                if ($c.datatype.name -eq 'DateTime')
                                { $itm =$r.item($c).tostring("yyyy-MM-dd hh:mm:ss.fff");}
                                else
                                {$itm = $r.item($c).tostring().trim();}
                            
                            }


                            $itm=$itm.replace("'", "''");
                         
                            if ($itm -eq 'Null')
                            {$sqlcmd += "$c=NULL,";}
                            else
                            {

                                switch ($c.DataType.name) 
                                {
                                    {$_ -in ('Guid', 'String', 'DateTime')} {$sqlcmd +="$c="+"'" + $itm + "',"; break;} 
                                    {$_ -in ('int32', 'int64', 'Decimal', 'double')} {$sqlcmd += "$c="+$itm + ","; break;} 
                                    {$_ -in ('boolean')} {if ($r.item($c)) {$sqlcmd += "$c="+'1,'} else {$sqlcmd +="$c="+'0,';}; break;} 
                                    {$_ -in ('byte[]')} { $sqlcmd += "$c="+'0x'+[System.BitConverter]::ToString($r.item($c)).replace('-', '')+",";                                                    
                                                          break; 
                                                        }
                                   # {$_ -in ('DateTime')} {$sqlcmd +="$c="+"'" + $itm + "',"; break;} 
                                    

                                    default {$sqlcmd +="$c="+"'" + $r.item($c) + "',"; break;} 

                                }#switch
                            }#else, i.e. $itm ne  'Null'

                          }#column loop

                        $sqlcmd = $sqlcmd.remove($sqlcmd.Length-1) + "`r`n union all `r`n" ; # we want to remove the ending ,
                        $ret_value +=$sqlcmd;

                      } #row loop
                    
                    # remove the final line of " union all"
                    if ($ret_value.Length -gt 13) 
                    { $ret_value = $ret_value.Substring(0, $ret_value.Length-13); };
                }#table loop

            }# $result.Tables.count -gt 0
            else
            {
                Write-Output "No data returned";
                return;
            }

            Write-Output $ret_value;
            return;
    }
    catch
    {
        $ex = $_.Exception
        Write-Error "$ex.Message"
    }
}#Convert-QueryDataToSQL

Now let's test this with a complex example:

-- first create a test table with a few records
use tempdb
--drop table dbo.test
create table dbo.test (a int, b varbinary(10),  c decimal(10,2), d datetime2, e varchar(10), f  float, u uniqueidentifier, g datetime
, h money, i bigint, x xml, b1 bit, b2 binary, c1 char(3))
go
insert into dbo.test (a, b, c, d, e, f, u, g, h, i,x, b1, b2, c1)
select 1, 0x1a1b, 10.2, '2015-01-01', 'abc', 4.1, newid(), getdate(), 100.332, 120, '<h><name>abc1</name></h>',1, 0x1a, 'hoh'
union all
select 2, 0x2a2b, 20.2, '2012-01-01', 'abc2', 2.1, '2DDDDDDE-D222-4985-84B1-0BEB8106457A', getdate(), 200.332, 120, '<h><name>abc2</name></h>',0, 0x2a, '2oh'
union all
select 3, null, 30.33, '2012-03-01', 'abc2', 2.1, newid(), getdate(), 200.332, 120, '<h><name>abc3</name><gender>Male</gender></h>',1, 0x3a, NULL
GO

We can now run the following PS script in a PS command window to generate a .sql file in c:\Temp\a.sql.

Convert-QueryDataToSQL -ServerInstance . -Database tempdb -Query "select * from dbo.test" | out-file -FilePath c:\temp\a.sql -force

If we open c:\temp\a.sql in SSMS, we will get the following (note: I manually put a line break before column [g] to make the long lines shorter for easy reading).

Note: one technical challenge here is how to convert values of binary or varbinary data type, fortunately, sql binary and varbinary data type is converted to .net byte[] inside Database.ExecuteWithResults, and we can then use [System.BitConverter]::ToString to convert the byte[] data to string data.

Summary

In this tip, we used PowerShell and SMO to dynamically generate a SELECT script that contains the result set of a query, and we can use this select query to easily populate or port the data to other environments.

We can put multiple queries or stored procedures in the -Query parameter of this Convert-QueryDataToSQL function.

This PS script may need a little modification if you have columns with [DateTime2] data type and you need more precision. The current PS script has a default precision of 3 digits after second, i.e. YYYY-MM-DD hh:mm:ss.xxx, only 3 digits for milliseconds, but [datetime2] can have up to 7 digits for milliseconds.

Next Steps

You can revise the PS script and test with other column data types that are not addressed in this tip, such as geometry, hierarchyid, DATETIMEOFFSET, and even UDT types and make it suitable to your own environment.

You may further test the script using multiple queries and stored procedures like sp_who2 / sp_whoisactive, for the -Query parameter and see the results.

You can also read the following similar articles:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2016-05-13

Friday, March 3, 2017 - 11:13:14 AM - jeff_yao	Back To Top (47068)
Thanks @Flemming. really appreciate your efforts to make the script more useful, no doubt a little contribution from each of us will make our sql server community better.