SQL server获取某个表所有数据的insert语句

创建存储过程

CREATE proc [dbo].[usp_Tool_GetSQL] 
@tablename sysname,  
@aimtablename  sysname = '',  
@condition  nvarchar(4000)  = '',  
@showSql char(1)   = 'N'  ,
@columnnames nvarchar(4000) ='*'
AS
 
begin  
 declare @sqlName varchar(max)  
 declare @sqlValues varchar(max)  
 declare @strCondition varchar(1000)  
  
 SET NOCOUNT ON  
   
 select @sqlName =' ('  
 select @sqlValues = 'VALUES (''+'  
 if(@aimtablename = '')  
  select @aimtablename = @tablename  
 select @sqlValues = @sqlValues + ColValue + ' + '','' + ' ,@sqlName = @sqlName + '[' + ColName + '],' 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) --smalldatetime datetime  
    --then 'case when ['+ name +'] is null then ''NULL'' else '+''''''''' + ' + 'cast(['+ name +'] as varchar)'+ '+'''''''''+' end'  
    then 'case when ['+ name +'] is null then ''NULL'' else '+''''''''' + ' + 'convert(nvarchar(24),['+ name +'],121)'+ '+'''''''''+' end'
    --  when xtype in (167,175)--(var)char  
    --     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+''''''''' + ' end'  
    when xtype in (167,175)--(var)char  
     then 'case when ['+ name +'] is null then ''NULL'' else '+'''N'''''' + ' + 'replace(['+ name+'],'''''''','''''''''''')' + '+''''''''' + ' end'  
    when xtype in (231,239)--(nvar)char  
     then 'case when ['+ name +'] is null then ''NULL'' else '+'''N'''''' + ' + 'replace(['+ name+'],'''''''','''''''''''')' + '+''''''''' + ' end'  
    else '''NULL'''  
   end as ColValue,name as ColName  
  from syscolumns    
  where id = object_id(@tablename) 
  AND (@columnnames='*' or CHARINDEX(','+name+',',','+@columnnames+',')>0)
   --and autoval is null --当该栏位为自增型int时，会出现autoval不为null的情况。  
  ) T  
 
  
--不同的DB计算出来的长度可能不一样，所以最后一个是逗号的话，多减去一个字符的长度
  select @sqlValues = left(@sqlValues,len(@sqlValues)-4)   
if left(reverse(@sqlValues),1) = ','
  select @sqlValues = left(@sqlValues,len(@sqlValues)-1)
  
 if(@showSql='Y')  
 BEGIN  
  print '--SQL1 - GenColoums:'  
  print '--select ''INSERT INTO ['+ @aimtablename + ']' + left(@sqlName,len(@sqlName)-1)+') ' + @sqlValues + ')'' AS INSERTSQL from '+@tablename + space(1) + @condition  
  print '--SQL2 - Not GenColoums:'  
  print '--select ''INSERT INTO ['+ @aimtablename + '] ' +  @sqlValues + ')'' AS INSERTSQL from '+@tablename  + space(1) + @condition   
 END  
 select @sqlName = left(@sqlName,len(@sqlName)-1)  
 select @strCondition=replace(@condition,'''','''''')  
 /*  
 --from table to table  
 exec ('SELECT ''--['+@tablename+']-->['+@aimtablename+']''  as [ ]')  
 --delete existed records   
 exec('select ''DELETE FROM'+ @tablename + ' ' + @strCondition+''' as [--Delete SQL]')  
 */  
 --get insert sql
 exec('SELECT ''--['+@tablename+']-->['+@aimtablename+']''  as [ ] UNION ' +   
  'SELECT ''DELETE FROM ['+ @tablename + '] ' + @strCondition+''' as [ ] UNION ' +   
  'SELECT ''INSERT INTO ['+ @aimtablename + ']' + @sqlName +') '+ @sqlValues + ')'' as [ ]  from '+ @tablename  + ' '  + @condition)  
  
 SET NOCOUNT OFF  
end 

调用

EXEC usp_Tool_GetSQL @tablename='数据表名称'

　　

堪称神器，支持中文数据表名