/** Object: StoredProcedure [dbo].[sp_GetInsertScript] Script Date: 08/21/2020 14:57:38 **/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    /
    exec sp_GetInsertScript ‘customer’,’ and 1=1’,’custno,custname’,1,1
    if(object_id(‘sp_GetInsertScript’)>0) drop proc sp_GetInsertScript
    /
    ALTER proc [dbo].[sp_GetInsertScript] (
    @tblname NVARCHAR(max), —表名
    @condition VARCHAR(max), —条件
    @fixcolumns VARCHAR(max)=’’,—指定列名
    @iscreatewhere INT = 1, —是否创建插入语句时增加where
    @IDENTITY_INSERT INT = 0 —默认不启用标识插入
    )
    as
    begin
    set nocount ON
    —PRINT @condition
    declare @sqlAll varchar(max)
    declare @sqlstr varchar(max)
    declare @sqlColumnValueForInsert varchar(max)
    declare @sqlColumnNameForInsert varchar(max)
    DECLARE @primarykey VARCHAR(500) —主键
    DECLARE @rid VARCHAR(500) —自增列
    declare @Sql nvarchar(max)
    DECLARE @columns VARCHAR(max) —非自增的所有列逗号分隔
    DECLARE @insertWhere VARCHAR(max)
    DECLARE @usedb VARCHAR(500)
    IF(ISNULL(@fixcolumns,’’)!=’’) SET @fixcolumns = ‘,’+@fixcolumns+’,’
    —连接数据库
    set @usedb =’’— CASE isnull(@dbname,’’) WHEN ‘’ THEN ‘’ ELSE ‘ use ‘+@dbname+ ‘ ‘ END
    —获得主键————————-
    SET @primarykey = ‘’
    SET @sql =@usedb + N’ SELECT @primarykey = @primarykey+’’,’’ + name FROM syscolumns col WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND
    EXISTS ( SELECT 1
    FROM dbo.sysindexes si
    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
    AND si.indid = sik.indid
    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
    AND sc.colid = sik.colid
    INNER JOIN dbo.sysobjects so ON so.name = si.name
    AND so.xtype = ‘’PK’’
    WHERE sc.id = col.id
    AND sc.colid = col.colid )
    ORDER BY col.colid’
    EXEC sp_executesql @sql,N’@primarykey varchar(500) out’,@primarykey out
    SET @primarykey = SUBSTRING(@primarykey,2,LEN(@primarykey ))
    —print @primarykey
    IF(CHARINDEX(‘,’,@primarykey)>0) set @primarykey = REPLACE(@primarykey,’,’,’+’)
    —获得自增列————————-
    —SELECT @rid = name FROM syscolumns WHERE OBJECT_NAME(id)=@tblname AND COLUMNPROPERTY(id, name, ‘IsIdentity’) = 1
    SET @sql =@usedb + N’ select @rid = name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND COLUMNPROPERTY(id, name, ‘’IsIdentity’’) = 1 ‘;
    EXEC sp_executesql @sql,N’@rid varchar(500) out’,@rid out
    —PRINT @rid
    IF(@primarykey = ‘’) SET @primarykey = @rid
    IF(isnull(@primarykey,’’)=’’)
    BEGIN
    PRINT ‘主键和自增列不能为空!’
    RETURN
    END
    —获得所有列
    SET @columns = ‘’
    —SELECT @columns = @columns+’,’+ + name FROM syscolumns WHERE OBJECT_NAME(id)=@tblname AND COLUMNPROPERTY(id, name, ‘IsIdentity’) = 0 ORDER BY colid
    —SET @sql =@usedb + N’ SELECT @columns = @columns+’’,’’+ name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ AND COLUMNPROPERTY(id, name, ‘’IsIdentity’’) = 0 ‘
    SET @sql =@usedb + N’ SELECT @columns = @columns+’’,’’+ name FROM syscolumns WHERE OBJECT_NAME(id)=’’’+@tblname+’’’ ‘
    IF(isnull(@fixcolumns,’’) != ‘’)
    BEGIN
    SET @sql = @sql + ‘ and ‘’’+@fixcolumns+’’’ like ‘’%,’’+name+’’,%’’ ‘
    END
    IF(@IDENTITY_INSERT = 0)
    BEGIN
    SET @sql = @sql + ‘ and ‘’,’+@rid+’,’’ not like ‘’%,’’+name+’’,%’’ ‘
    END
    SET @sql = @sql + ‘ order by colid ‘

    —PRINT @sql
    EXEC sp_executesql @sql,N’@columns varchar(max) out’,@columns out
    SET @columns = SUBSTRING(@columns,2,LEN(@columns ))
    —PRINT @columns

    select @sqlstr=’select ‘’insert ‘+@tblname
    select @sqlColumnValueForInsert=’’
    select @sqlColumnNameForInsert=’(‘
    —select @sqlColumnValueForInsert=’values (‘’+’
    select @sqlColumnValueForInsert=’select ‘’+’
    SELECT @insertWhere = ‘ +’’ where not exists(select 1 from ‘+@tblname+’ where ‘+@primarykey+’ = ‘’’’’’+convert(varchar,’+@primarykey+’)+’’’’’’)’’ ‘
    select @sqlColumnValueForInsert=@sqlColumnValueForInsert+col+’+’’,’’+’ ,@sqlColumnNameForInsert=@sqlColumnNameForInsert+name +’,’
    from (select case
    when a.xtype =173 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =104 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =175 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
    when a.xtype =61 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’convert(varchar,’+a.name +’,121)’+ ‘+’’’’’’’’’+’ end’
    when a.xtype =106 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =62 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’,2)’+’ end’
    when a.xtype =56 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =60 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =239 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
    when a.xtype =108 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =231 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
    when a.xtype =59 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’,2)’+’ end’
    when a.xtype =58 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’convert(varchar,’+a.name +’,121)’+ ‘+’’’’’’’’’+’ end’
    when a.xtype =52 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =122 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =127 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =48 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =165 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’convert(varchar,’+a.name +’)’+’ end’
    when a.xtype =167 then ‘case when ‘+a.name+’ is null then ‘’NULL’’ else ‘+’’’’’’’’’+’+’replace(‘+a.name+’,’’’’’’’’,’’’’’’’’’’’’)’ + ‘+’’’’’’’’’+’ end’
    else ‘’’NULL’’’
    end as col,a.colid,a.name
    from syscolumns a where a.id = object_id(@tblname)
    and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
    )t
    WHERE 1=1 AND ‘,’+ @columns + ‘,’ LIKE ‘%,’+t.name+’,%’
    order by colid

    —PRINT @sqlColumnValueForInsert
    —select @sqlstr=@sqlstr+left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘+left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-3)+’)’’ from ‘+@tblname + ‘ where 1=1 and ‘ + isnull(@condition,’’)
    SET @sqlAll = ‘’;
    set @sqlAll = @sqlAll + @usedb
    IF(@IDENTITY_INSERT = 1) SET @sqlAll = @sqlAll + ‘ select ‘’aa—- SET IDENTITY_INSERT ‘+@tblname+’ ON ‘’ union ‘
    set @sqlAll = @sqlAll + @sqlstr
    set @sqlAll = @sqlAll + left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘
    set @sqlAll = @sqlAll + left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-5)
    IF(@iscreatewhere = 1) set @sqlAll = @sqlAll + @insertWhere
    set @sqlAll = @sqlAll + ‘ from ‘+ @tblname + ‘ where 1=1 ‘
    IF(isnull(@condition,’’) != ‘’) SET @sqlAll = @sqlAll + isnull(@condition,’’)
    IF(@IDENTITY_INSERT = 1) SET @sqlAll = @sqlAll + ‘ union select ‘’zz—- SET IDENTITY_INSERT ‘+@tblname+’ OFF ‘’ ‘
    / set @sqlstr=@sqlstr+
    left(@sqlColumnNameForInsert,len(@sqlColumnNameForInsert)-1)+’) ‘+
    left(@sqlColumnValueForInsert,len(@sqlColumnValueForInsert)-5)+
    case @iscreatewhere when 1 then @insertWhere ELSE ‘’ END +
    ‘ from ‘+ @tblname + ‘ where 1=1 and ‘ + isnull(@condition,’’)
    /
    print @sqlAll
    exec( @sqlAll)
    PRINT ‘总记录数:’+convert(varchar,case when @IDENTITY_INSERT=1 then @@rowcount - 2 else @@rowcount end)
    / SELECT ‘SET IDENTITY_INSERT ‘+@tblname+’ ON’
    SELECT ‘SET IDENTITY_INSERT ‘+@tblname+’ OFF’
    /
    set nocount off
    END