有个缺点……就是标识种子的列 也insert了
create 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 '+@tablename
--print @sql
exec (@sql)
end
go
