复制表数据

create proc [dbo].[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 

 

 

 

exec spGenInsertSQL tableName

 

 

 

 

 

或者

 

select 'insert into Person(Name,Description) values(
'''+cast(Name as varchar(100))+''','''+cast(Description as varchar(100))+'''
)'
from Person