create proc spgeninsertsql @tablename as varchar(100) as --declare @tablename varchar(100) --set @tablename = 'orders' --set @tablename = 'eeducation' declare xcursor cursor for select name,xusertype from syscolumns where (id = object_id(@tablename)) declare @f1 varchar(100) declare @f2 integer declare @sql varchar(8000) set @sql ='select ''insert into ' + @tablename + ' values(''' open xcursor fetch xcursor into @f1,@f2 while @@fetch_status = 0 begin set @sql [email protected] + + case when @f2 in (35,58,99,167,175,231,239,61) then ' + case when ' + @f1 + ' is null then '''' else '''''''' end + ' else '+' end + 'replace(isnull(cast(' + @f1 + ' as varchar),''null''),'''''''','''''''''''')' + case when @f2 in (35,58,99,167,175,231,239,61) then ' + case when ' + @f1 + ' is null then '''' else '''''''' end + ' else '+' end + char(13) + ''',''' fetch next from xcursor into @f1,@f2 end close xcursor deallocate xcursor set @sql = left(@sql,len(@sql) - 5) + ' + '')'' from ' + @tablename print @sql exec (@sql)
第二版:2003.03.08
alter proc spgeninsertsql (@tablename varchar(256)) as begin declare @sql varchar(8000) declare @sqlvalues varchar(8000) 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 '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' 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 ='select ''insert into ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlvalues,len(@sqlvalues)-4) + ')'' from '[email protected] --print @sql exec (@sql) end
第三版: 2003.3.9
alter proc spgeninsertsql (@tablename varchar(256)) as begin declare @sql varchar(8000) declare @sqlvalues varchar(8000) 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 '+''''''''' + ' + '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 ''insert into ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlvalues,len(@sqlvalues)-4) + ')'' from '[email protected] print @sql exec (@sql) /* select * from syscolumns where id = object_id('test') and autoval is null */ end
