Create Insert SQL Statement from the Records in an Existing Table

Introduction

Sometimes, you want to export some SQL scripts of some records from some existing tables in your SQL Server database so that you can insert those data into a similar table of another database. There are lots of ways to do that. Here, I will share you a stored procedure to do that. Hope it helps.

Using the Code

First, please create the below stored procedure [dbo].[sp_CreateInsertScript] in your SQL Server database.

[dbo].[sp_CreateInsertScript] content:

--=============================================
-- Author: Mark Kang
-- Company: www.ginkia.com
-- Create date: 2016-03-06
-- Description: Generat the insert sql script according to the data in the specified table.
--              It does not support the columns with timestamp,text,image.
-- Demo : exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''
-- Change History:
--         1.2016-03-06 Created and published
--         2.2016-03-08 Based on Mike's suggestions, I optimized the codes
--         3.2019-03-09 1)Add code lines to avoid error when @con is empty string
--                      2)Based on Lukas Macedo's suggetstions, add surrounding brackets for column name
--                      3)Simplify WHEN...CASE
-- =============================================
CREATE PROC [dbo].[sp_CreateInsertScript] (
    @tablename NVARCHAR(256) -- table name
    ,@con NVARCHAR(400) -- condition to filter data
    ,@ignoreIdentityCol bit=0 --indicate if ignore columne with identity
    ,@isDebug bit=0 --indicate if this is used to debug. when 1,output the internal sql string
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sqlstr NVARCHAR(MAX);
    DECLARE @valueStr1 NVARCHAR(MAX);
    DECLARE @colsStr NVARCHAR(MAX);
    SELECT @sqlstr='SELECT ''INSERT '+@tablename;
    SELECT @valueStr1='';
    SELECT @colsStr='(';
    SELECT @valueStr1='VALUES (''+';

    IF RTRIM(LTRIM(@con))=''
        SET @con='1=1';

    SELECT @valueStr1=@valueStr1+col+'+'',''+' 
            ,@colsStr=@colsStr+'['+name +'],' 
    FROM (
            SELECT 
                    CASE
                        /* xtype=173 'binary'
                        xtype=165 'varbinary'*/
                        WHEN sc.xtype in (173,165) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.[length]*2+2)+'),['+sc.name +'])'+' END'                        
                        /*xtype=104 'bit'*/
                        WHEN sc.xtype =104 THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(1),['+sc.name +'])'+' END'                        
                        /*xtype=61 'datetime'
                        xtype=58 'smalldatetime'*/
                        WHEN sc.xtype in(58,61) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'CONVERT(NVARCHAR(23),'+sc.name +',121)'+ '+'''''''''+' END'
                        /*xtype=175 'char'
                        xtype=36 'uniqueidentifier'
                        xtype=167 'varchar'
                        xtype=231 'nvarchar'
                        xtype=239 'nchar'*/
                        WHEN sc.xtype in (36,175,167,231,239) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE(['+sc.name+'],'''''''','''''''''''')' + '+'''''''''+' END'
                        /*xtype=106 'decimal'
                        xtype=108 'numeric'*/
                        WHEN sc.xtype in(106,108) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.xprec+2)+'),['+sc.name +'])'+' END'
                        /*xtype=59 'real'
                        xtype=62 'float'*/
                        WHEN sc.xtype in (59,62) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),'+sc.name +',2)'+' END'
                        /*xtype=48 'tinyint'
                        xtype=52 'smallint'
                        xtype=56 'int'
                        xtype=127 'bigint'
                        xtype=122 'smallmoney'
                        xtype=60 'money'*/
                        WHEN sc.xtype in (48,52,56,127,122,60) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),['+sc.name +'])'+' END'
                        ELSE '''NULL'''
                    END    AS col
                ,sc.colid
                ,sc.name
            FROM syscolumns AS sc 
            WHERE sc.id = object_id(@tablename) 
            AND sc.xtype <>189 --xtype=189 'timestamp' 
            AND sc.xtype <>34 --xtype=34 'image' 
            AND sc.xtype <>35 --xtype= 35 'text'
            AND (columnproperty(sc.id, sc.name, 'IsIdentity') = 0 OR @ignoreIdentityCol=0)
            ) AS t 
    ORDER BY colid;
        
    SET @colsStr=left(@colsStr,len(@colsStr)-1)+') ';
    SET @valueStr1=left(@valueStr1,len(@valueStr1)-3)+')''';

    SELECT @sqlstr=@sqlstr+@colsStr+@valueStr1+' AS sql FROM '+@tablename +  ' WHERE 1=1 AND  ' + isnull(@con,'1=1');

    IF @isDebug=1
    BEGIN
        PRINT '1.columns string: '+ @colsStr;
        PRINT '2.values string: '+ @valueStr1
        PRINT '3.'+@sqlstr;
    END

    EXEC( @sqlstr);
    SET NOCOUNT OFF
END
GO

Example

Next, I do an example to help you understand how to use it. We assume you have a table Country as below in your database and you want get insert SQL statements from some records in this table. These records should be items that their continent_name is North America.

Example table script:

CREATE TABLE [dbo].[Country](
    [geoname_id] [varchar](50) NULL,
    [locale_code] [varchar](50) NULL,
    [continent_code] [varchar](50) NULL,
    [continent_name] [varchar](50) NULL,
    [country_iso_code] [varchar](50) NULL,
    [country_name] [varchar](50) NULL
) ON [PRIMARY]

According to your requirements, you can call the stored procedure you just created to generate SQL scripts like this:

exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''

After that, you will get some insert SQL statement records:

By now, you can copy the records or save the output to a query or txt file by commands in right-click menu so that you use it in another database.

Thanks!