1 CREATE proc spGenInsertSQL (@tablename varchar(256)) 2 as 3 begin 4 declare @sql varchar(8000) 5 declare @sqlValues varchar(8000) 6 set @sql =' (' 7 set @sqlValues = 'values (''+' 8 select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],' 9 from 10 (select case 11 when xtype in (48,52,56,59,60,62,104,106,108,122,127) 12 13 then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end' 14 15 when xtype in (58,61) 16 17 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end' 18 19 when xtype in (167) 20 21 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' 22 23 when xtype in (231) 24 25 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' 26 27 when xtype in (175) 28 29 then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end' 30 31 when xtype in (239) 32 33 then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end' 34 35 else '''NULL''' 36 37 end as Cols,name 38 39 from syscolumns 40 41 where id = object_id(@tablename) 42 43 ) T 44 set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename 45 print @sql 46 exec (@sql) 47 end 48 GO
使用方法: exec spGenInsertSQL '表名'
