sql 复制含自动编号的表

 通常情况下，我们可以直接通过如下语句来复制表

insert into mytb select * from mydb.dbo.mytb

 

如果数据表中带有自动编号的列，则出现“消息 8101，级别 16，状态 1，第 1 行
仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'mytb'中的标识列指定显式值。”

 

当表的列数不多，可以通过如下方式来实现数据导入

insert into mytb select 列名列表（除去自动编号的列） from mydb.dbo.mytb

 

如果列很多，那实现起来就很麻烦，下面是自己经过一个晚上的研究写的一个存储过程，可以方便的实现数据导入，只需要三个参数。

/*
SQL复制含自动编号的表
问题：数据表在设计时，将列设成自动编号，当系统升级时，将原数据导入新库中将十分麻烦，这里通过一个方法简化这个操作过程
*/
create procedure CopyTableData
(
@tbName varchar(100),--表名
@olddbName varchar(100),--原数据库名
@newdbName varchar(100)--新数据库名 如:mydb
)
as
--创建临时表，保存表的所有列名，为实现字符串合并处理做准备（获取所有列名的组成字符串）
--先判断表是否存在，存在则先删除
if object_id('tempdb..#coltable') is not null  
   drop table #coltable
create table #coltable(id int,value varchar(100))
--获取表中的所有列名并存入临时表中
insert into #coltable
select 1,name
from syscolumns
where id=object_id(''+@tbName+'')
declare @colName nvarchar(4000)
--将行合并成一列,组成列名字符串  
select @colName=[values] from(select distinct id from #coltable)A
 outer apply(select [values]=stuff(replace(replace((
    select value from #coltable N  where id = A.id  for XML auto), '<N value="', ','), '"/>', ''), 1, 1, ''))N
declare @sql nvarchar(4000)
--将数据导入新表
set @sql='set identity_insert '+@tbName+' on; '
set @sql=@sql+' insert into '+@newdbName+'.dbo.'+@tbName+'('+@colName+')
 select '+@colName+' from '+@olddbName+'.dbo.'+@tbName
set @sql=@sql+' ;set identity_insert '+@tbName+' off;'
exec(@sql)
drop table #colTable
return