将数据库中的数据转换为insert语句

将数据库中的数据转换成insert语句的T-SQL代码如下：

if exists(select * from sys.objects where name = 'sp_generate_insert_script')
 drop proc sp_generate_insert_script
go
create procedure sp_generate_insert_script
 @table_list varchar(8000)='*'
as
declare @table_name  nvarchar(128)
declare @column_list varchar(8000)
declare @values_list varchar(8000)
declare @sql   varchar(8000)
declare @msg   varchar(8000)
create table #result(sql varchar(8000))
 if @table_list='*'
 begin
  select @table_list=@table_list+','+name from sys.objects where type='u' and name <> 'dtproperties'
  set @table_list=stuff(@table_list,1,2,'')
 end
 while @table_list<>''
 begin
 if charindex(',',@table_list)>0
  select @table_name=left(@table_list,charindex(',',@table_list)-1),
   @table_list=stuff(@table_list,1,charindex(',',@table_list),'')
 else
  select @table_name=@table_list,
   @table_list=''
  if exists(select 1 from sysobjects where xtype='u' and name=@table_name)
  begin
   select @column_list='',@values_list=''
   select @column_list=@column_list+','+name,@values_list=@values_list+'+'',''+'+
    case when xtype in(175,167,36) then--char,varchar,uniqueidentifier
     'isnull(''''''''+replace('+name+','''''''','''''''''''')+'''''''',''null'')'
     when xtype in(239,231) then--nchar,nvarchar
     'isnull(''n''''''+replace('+name+','''''''','''''''''''')+'''''''',''null'')'
     when xtype in(61,58) then--datetime,smalldatetime
     'isnull(''''''''+convert(char(23),'+name+',121)+'''''''',''null'')'
    else--digital
     'isnull(convert(varchar(20),'+name+'),''null'')'
   end
   from (select a.name,a.xtype from syscolumns a,sysobjects b where b.xtype='u'
    and b.name=@table_name and a.id=b.id
    and a.xtype not in(173,165,34,35,99,98,189)
   )t
   select @column_list=stuff(@column_list,1,1,''),
    @values_list=stuff(@values_list,1,4,''),
    @sql='select ''insert into '+@table_name+'('+@column_list+')'
    +' values('''+@values_list+'+'')'' sql from ['+@table_name+']'
   if objectproperty(object_id(@table_name),'tablehasidentity')=1
    insert into #result(sql)
     exec('select ''--table name: '+@table_name+''' sql union all '
     +'select ''set identity_insert '+@table_name+' on '' sql union all '
     +@sql+' union all '
     +'select ''set identity_insert '+@table_name+ ' off '' sql')
   else
    insert into #result(sql)
     exec('select ''--table name: '+@table_name+''' sql union all '+@sql)
  end
  else
  begin
   set @msg='can''t generate the insert script of the table '''+@table_name
    +''', because it does not exist in the system catalog.'
   drop table #result
   raiserror(@msg,16,1)
   return
  end
 end
 select sql from #result
 drop table #result
go

 

调用格式举例：
sp_generate_insert_script 'table1'--转换表table1中的数据
sp_generate_insert_script 'table1,table2,table3'--转换表table1,table2,table3中的数据
sp_generate_insert_script '*'--转换所有表中的数据

 

 

优点：
1.支持三种参数格式，调用方便
2.支持标识列的导出
3.将结果放在一个结果集中，方便拷贝

缺点：
1.不支持以下类型：binary,varbinary,image,text,ntext,sql_variant,timestamp
2.由于受sql server字符串长度影响（8000b），若表中字段过多或表中数据过长会被截断而出错，但一般的需求可以满足，最大可以支持每表100个字段左右，每条记录最大支持为6-7kb（视字段多少及字段名称长度而定）