Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generate INSERT scripts from SQL Server queries and stored procedure output


By:   |   Last Updated: 2016-05-13   |   Comments (2)   |   Related Tips: 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
1John Doe32
2Jane Doe40
3Mary Smith23

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:

  1. 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]
  2. 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.

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

Script out Query Reult

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:



Last Updated: 2016-05-13


next webcast button


next tip button



About the author
MSSQLTips author Jeffrey Yao 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 03, 2017 - 11:13:14 AM - jeff_yao Back To Top

 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.


Friday, March 03, 2017 - 2:22:49 AM - Flemming Thor Hansen Back To Top

 Hi Jeffrey

Really helpful code. I managed to get my automated script done. I added a new parameter to be able to insert text at beginning of script like TRUNCATE TABLE xxx; INSERT xxx. Please find my script below

Cheers.

/Flemming

<#

.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

    The SQL Server instance name, which the query will connect to

 

   -Database []

    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,

        # INSERT

        [Parameter(Mandatory=$true,

                   Position=3)]

        [String] $Insert

 

 

)

    

    [string[]]$columns='';

    [string] $sqlcmd = '';

    [string] $ret_value= $Insert;

 

    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

 

Convert-QueryDataToSQL -ServerInstance Server1 -Database Database -Query 'SELECT * FROM [Database].[schema].[Tablename]' -insert "TRUNCATE TABLE [Database].[schema].[Tablename];`r`nINSERT [Database].[schema].[Tablename]`r`n" | out-file '\\Sharename\Server1\Database_Tablename_Data.sql'

 

 


Learn more about SQL Server tools