通过存储过程生成insert语句

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Z_SP_GenInsertSQL]
(
    @TableName varchar(256)
    ,@AllTopClause varchar(1000) = ''
    ,@WhereOrderByClause varchar(1000) = '' --'where 1 = 1 order by null'
)
as
begin
/**//*
usage:
Z_SP_GenInsertSQL 'employees','all top 30 PERCENT with ties ','where [LastName] is not null order by employeeid desc'
*/
    declare @sql varchar(8000)
    declare @sqlValues varchar(8000)
    set @sql = ' ''(''' + char(13) + ','
    set @sqlValues = ' values ('''+ char(13) + ','
    select @sqlValues = @sqlValues + cols + ' + '',' + '''' + char(13) + ','
            ,@sql = @sql + '''[' + name + '],''' + char(13) + ','
    from
    (
        select
            case
                when xtype in (48,52,56,59,60,62,104,106,108,122,127)
                    then 'case when ' + name + ' is null then ''NULL'' else ' + 'cast(' + name + ' as varchar)' + ' end'
                when xtype in (58,61)
                    then 'case when ' + name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'cast(' + name + ' as varchar)' + '+''''''''' + ' end'
                when xtype in (167,175)
                    then 'case when ' + name + ' is null then ''NULL'' else ' + ''''''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
                when xtype in (231,239)
                    then 'case when ' + name + ' is null then ''NULL'' else ' + '''N'''''' + ' + 'replace(' + name + ','''''''','''''''''''')' + ' + ''''''''' + ' end'
                else '''NULL'''
            end as Cols
            ,name
        from syscolumns
        where id = object_id(@TableName)
                --and autoval is null --忽略自增整型字段
    ) T
    set @sql = 'select ' + @AllTopClause + char(13) + '''INSERT INTO ''' + char(13) + ','
                 + '''['+ @TableName + ']''' + char(13) + ','
                 + left(@sql,len(@sql)-4) + '''' + char(13) + ','')' + left(@sqlValues,len(@sqlValues)-7) + ','')'''
                 + char(13) + 'from [' + @TableName + ']'
                 + char(13) + @WhereOrderByClause
    --select @sql -- select SQL 被截断
    print @sql -- print SQL 是完整正确的
    exec (@sql)
/**//*
select *
from syscolumns    
where id = object_id('test') and autoval is null
*/
end