getinsertsql.sql

将SQLSERVER记录还原生成Insert的SQL语句

if exists(select 1 from sysobjects where name = 'getInsertSQL') 
  drop procedure getinsertsql 
go 

CREATE procedure getInsertSQL  
(  
    @TableName varchar(256)  
    ,@AllTopClause varchar(1000) = ''  
    ,@WhereOrderByClause varchar(1000) = '' --'where 1 = 1 order by null'  
)  
as  
  
/**//*  
usage:  
Z_SP_GenInsertSQL 'employees','all top 30 PERCENT with ties ','where [LastName] is not null order by employeeid desc'  
*/  
    declare @sql varchar(8000) , @maxcols int ,@curcolorder int   
    declare @sqlValues1 varchar(8000) , @sqlValues2 varchar(8000) , @sqlValues3 varchar(8000)   
    set @sql = ' ''('''+ char(13) + ','  
    select @sqlValues1 = ''   
    select @sqlValues2 = ''   
  
    select @sqlValues3 = ''   
    select @maxcols = max(colorder) from    syscolumns     where id = object_id(@TableName)   
  
    select    @sqlValues1 = case when colorder<=@maxcols/3 then @sqlValues1 + cols + ' + '',' + '''' + char(13) + ',' else @sqlValues1 end   
            , @sqlValues2 =  case when colorder>@maxcols/3 and colorder<=@maxcols*2/3 then @sqlValues2 + cols + ' + '',' + '''' + char(13) + ',' else @sqlValues2 end   
            , @sqlValues3 = case when colorder>@maxcols*2/3 then  @sqlValues3 + cols + ' + '',' + '''' + char(13) + ',' else @sqlValues3 end   
            ,@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 , colorder    
        from syscolumns  
        where id = object_id(@TableName)  
                and autoval is null --忽略自增整型字段  
    ) T  
    set @sqlValues1 = ' values ('''+ char(13) + ',' + @sqlValues1   
  
    set @sql = 'select ' + @AllTopClause + char(13) + '''INSERT INTO ''' + char(13) + ','  
                 + '''['+ @TableName + ']''' + char(13) + ','  
                 + left(@sql,len(@sql)-4) + '''' + char(13) + ','')'   
  
  
    select @sqlValues3 = left(@sqlValues3,len(@sqlValues3)-7) + ','')'''  
                 + char(13) + 'from [' + @TableName + ']'  
                 + char(13) + @WhereOrderByClause  
    --select @sql -- select SQL 被截断  
  
     
    print @sql + @sqlValues1 + @sqlValues2 + @sqlValues3 -- print SQL 是完整正确的  
    exec ( @sql + @sqlValues1 + @sqlValues2 + @sqlValues3)