获取Insert语句的存储过程

SQL Server Enterprise Manager本身提供了生成各种对象Script的功能，却唯独没有生成Insert语句的功能，一直以来让我耿耿于怀！后来看到一位网友采用SP倒出insert语句的代码，自己略加整理和完善，不敢敝帚自珍，现在与大家分享：

     该SP可以倒出整个表所有的数据的insert语句，也可以根据条件倒出部分。不足之处在于不支持一些特殊类型的数据倒出，如text,image等。

     在SQL Query Analyzer中执行的时候，需要设置一些参数，以便更好地看到效果，如下图：

/*

Sample:

在Northwind数据库生成SP后，执行下面的SQL

exec GetInsertSQL  'Customers','','where CustormerID like ''AN%'''

*/

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE    procedure GetInsertSQL
 @tablename sysname,
 @aimtablename  sysname = '',
  @condition  nvarchar(500)  = '',
 @showSql char(1)   = 'N'
as
begin
 declare @sqlName varchar(8000)
 declare @sqlValues varchar(8000)
 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'
    --  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 autoval is null --当该栏位为自增型int时，会出现autoval不为null的情况。
  ) T

 select @sqlValues = left(@sqlValues,len(@sqlValues)-4)
 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

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO