(Sql server) create Sql Insert statement with data in existing table

Previously, a stored procedure for creating Sql statements of Insert with data in existing tables was published in Codeproject. Today, it is moved here. Note that this stored procedure is only applicable to SQL SERVER.

introduce

Sometimes, you want to export Sql scripts for records in existing tables so that you can insert the data into similar tables in another database. There are many ways to do it. Now, I will share a stored procedure with you to implement it. I hope it will help you.

Code

First, create the following stored procedure in your SQL Server database named [dbo]. [sp  createinsertscript]

[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, let me give you an example to help you understand how to use it. Suppose there is a table Country in your database. You want to get the SQL statement for inserting some data records in this table. The record filter condition is the record whose value of the column contract name is North America. The creation script of the table is as follows:

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]

Next, by calling the stored procedure you created, execute it with the following statement to generate the insert statement of the SQL you want. The call execution script is as follows:

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

After execution, you will get the result you want. The picture below is a screenshot of my computer.

Now, you can copy these results or keep the output as a query or text file through the right-click menu option for you to use. Thanks!

Tags: SQL Sever SQL Stored Procedure Database

Posted on Sat, 09 Nov 2019 15:43:26 -0500 by Warptweet