数据库复制

/*--数据库数据复制

 将一个数据库中的数据复制到另一个数据库
 如果某列在目标数据库中为标识列,将不会被复制

 适用范围:数据库结构发生了变化,想将旧数据库进行升级
  这样就可以根据新的数据库结构创建一个空库,然后
  将旧数据库的所有数据复制到新库中
--*/

/*--调用示例

 exec p_copydb @#源数据库@#,@#目标数据库@#
 exec p_copydb @#aclearcase/" target="_blank" >cc_五医@#,@#acc_演示数据8@#
--*/

if exists (select * from dbo.sysobjects where id = object_id(N@#[dbo].[p_copydb]@#) and OBJECTPROPERTY(id, N@#IsProcedure@#) = 1)
drop procedure [dbo].[p_copydb]
GO

create proc p_copydb
@o_dbname sysname,  --要复制数据的数据库--源数据库
@n_dbname sysname,  --接收数据的数据库--目标数据库
@cleardb bit=0   --清空目标数据库
as
declare @sql nvarchar(4000)

--禁用约束,防止复制时的数据冲突
set @sql=@#declare #tbc cursor for select name,tbname=object_name(parent_obj)
 from @#+@n_dbname+@#..sysobjects where xtype in(@#@#C@#@#,@#@#F@#@#)@#
exec(@sql)
declare @name sysname,@tbname sysname
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
 set @sql=@#alter table @#+@n_dbname+@#..[@#+@tbname+@#] NOCHECK CONSTRAINT [@#+@name+@#]@#
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc

--复制数据
declare @sql1 varchar(8000)
set @sql=@#declare #tb cursor for select a.name from @#
 +@o_dbname+@#..sysobjects a inner join @#
 +@n_dbname+@#..sysobjects b on a.name=b.name
  where a.xtype=@#@#U@#@# and b.xtype=@#@#U@#@#@#
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
 select @sql1=@#@#
  ,@sql=@#select @sql1=@sql1+@#@#,[@#@#+a.name+@#@#]@#@# from(
  select name from @#+@o_dbname+@#..syscolumns where id in
  (select id from @#+@o_dbname+@#..sysobjects where name=@#@#@#+@tbname+@#@#@#)
 ) a inner join (
  select name from @#+@n_dbname+@#..syscolumns where status<>0x80 and id in
  (select id from @#+@n_dbname+@#..sysobjects where name=@#@#@#+@tbname+@#@#@#)
 ) b on a.name=b.name@#
 exec sp_executesql @sql,N@#@sql1 nvarchar(4000) out@#,@sql1 out

 select @sql1=substring(@sql1,2,8000)
 exec(@#insert into @#+@n_dbname+@#..[@#+@tbname+@#](@#+@sql1
  +@#) select @#+@sql1+@# from @#+@o_dbname+@#..[@#+@tbname+@#]@#)
 if @@error<>0
  print(@#insert into @#+@n_dbname+@#..[@#+@tbname+@#](@#+@sql1
   +@#) select @#+@sql1+@# from @#+@o_dbname+@#..[@#+@tbname+@#]@#)
 fetch next from #tb into @tbname
end
close #tb
deallocate #tb

--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
 set @sql=@#alter table @#+@n_dbname+@#..[@#+@tbname+@#] CHECK CONSTRAINT [@#+@name+@#]@#
 exec(@sql)
 fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go

原文转自：http://www.ltesting.net