數據庫復制
2024-07-21 02:08:43
供稿：網友
/*--數據庫數據復制
將一個數據庫中的數據復制到另一個數據庫
如果某列在目標數據庫中為標識列,將不會被復制
適用范圍:數據庫結構發生了變化,想將舊數據庫進行升級
這樣就可以根據新的數據庫結構創建一個空庫,然后
將舊數據庫的所有數據復制到新庫中
--*/
/*--調用示例
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, 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 '[email protected]_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 '[email protected]_dbname+'..['[email protected]+'] nocheck constraint ['[email protected]+']'
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 '
[email protected]_dbname+'..sysobjects a inner join '
[email protected]_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 @[email protected]+'',[''+a.name+'']'' from(
select name from '[email protected]_dbname+'..syscolumns where id in
(select id from '[email protected]_dbname+'..sysobjects where name='''[email protected]+''')
) a inner join (
select name from '[email protected]_dbname+'..syscolumns where status<>0x80 and id in
(select id from '[email protected]_dbname+'..sysobjects where name='''[email protected]+''')
) 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 '[email protected]_dbname+'..['[email protected]+']('[email protected]
+') select '[email protected]+' from '[email protected]_dbname+'..['[email protected]+']')
if @@error<>0
print('insert into '[email protected]_dbname+'..['[email protected]+']('[email protected]
+') select '[email protected]+' from '[email protected]_dbname+'..['[email protected]+']')
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 '[email protected]_dbname+'..['[email protected]+'] check constraint ['[email protected]+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go
