IDC资讯中心
/*–数据库数据复制
将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制
适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
–*/
/*–调用示例
exec p_copydb 源数据库,目标数据库
exec p_copydb acc_五医,acc_演示数据8
–*/
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[p_copydb]) and objectproperty(id, nisprocedure) = 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
