（Sql server）用现有表中的数据创建Sql的Insert插入语句[通俗易懂]

[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

代码100分

代码100分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]

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