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

----------生成自带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))
 )T

set @sql ='select ''UPDATE ['+ @tablename + '] SET' + @sqlValues + ' from ['+@tablename +']'
print @sql
exec (@sql)

总结

以上是生活随笔为你收集整理的将表中的数据自动生成INSERT、UPDATE语句的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。