将表中的数据自动生成INSERT、UPDATE语句

原创

草宝虫啊 2023-05-17 11:50:41 ©著作权

©著作权归作者所有：来自51CTO博客作者草宝虫啊的原创作品，请联系作者获取转载授权，否则将追究法律责任

----------生成自带SET IDENTITY_INSERT 的sql语句，处理不同操作系统间时间导入问题

declare @tablename varchar(256)
set @tablename='D_LawDocuType_TB'
declare @ident int
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
declare @identUp varchar(1000)
declare @identDown varchar(1000)
SELECT @identUp='',@identDown=''select @ident=status&0x80 from syscolumns where id=object_id(@tablename) and status&0x80=0x80
if(@ident is not null)
BEGIN
 select @identUp='SELECT ''SET IDENTITY_INSERT ['+@tablename+'] ON'' UNION ALL ',
   @identDown=' UNION ALL SELECT ''SET IDENTITY_INSERT ['+@tablename+'] OFF'''
END
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
  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 '+''''''''' + ' + 'CONVERT(varchar(100), '+ name +',120)'+ '+'''''''''+' end'
               when xtype in (167)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (231)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (175)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                when xtype in (239)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                else '''NULL'''
              end as Cols,name
         from syscolumns 
        where id = object_id(@tablename)
      ) T
set @sql =@identUp   +'select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from ['+@tablename +']'+
   +@identDown
print @sql
exec (@sql) 
----生成UPDATE语句，未经过严格测试
declare @tablename varchar(256)
set @tablename='B_TrainFlow_TB'
declare @sql varchar(8000)
declare @sqlValues varchar(8000)set @sqlValues = ' '
select @sqlValues = @sqlValues + '[' + name + ']='' + ' + cols + ' + '',' 
  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 '+''''''''' + ' + 'CONVERT(varchar(100), '+ name +',120)'+ '+'''''''''+' end'
               when xtype in (167)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (231)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (175)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                when xtype in (239)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                else '''NULL'''
              end as Cols,name
         from syscolumns 
        where id = object_id(@tablename)
AND colid!=(select colid FROm  sysindexkeys where id = object_id(@tablename))
      ) T
--主键
select @sqlValues = left(@sqlValues,len(@sqlValues)-4) + '+'' WHERE [' + name + ']='' + ' + cols + '' 
  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 '+''''''''' + ' + 'CONVERT(varchar(100), '+ name +',120)'+ '+'''''''''+' end'
               when xtype in (167)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (231)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                when xtype in (175)
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                when xtype in (239)
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                else '''NULL'''
              end as Cols,name
  from syscolumns         where id = object_id(@tablename)
AND colid=(select colid FROm  sysindexkeys where id = object_id(@tablename))
 )Tset @sql ='select ''UPDATE ['+ @tablename + '] SET' + @sqlValues + ' from ['+@tablename +']'
print @sql
exec (@sql)