--如果只是简单的数据迭加,可以用下面的存储过程,将一个数据库的所有数据追加到另一个数据库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 /*--数据库数据复制 将一个数据库中的数据复制到另一个数据库 如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级 这样就可以根据新的数据库结构创建一个空库,然后 将旧数据库的所有数据复制到新库中 --邹建 203.10--*//*--调用示例 exec p_copydb 'bns_aa','bns_new' exec p_copydb 'acc_五医','acc_演示数据8' --*/ 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 from '+@n_dbname+'..sysobjects where xtype=''U'' and status>=0' exec(@sql)declare @tbname sysname open #tbc fetch next from #tbc into @tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ALL' exec(@sql) set @sql='alter table '+@n_dbname+'..['+@tbname+'] disable trigger ALL' exec(@sql) fetch next from #tbc into @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 @tbname while @@fetch_status=0 begin set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ALL' exec(@sql) set @sql='alter table '+@n_dbname+'..['+@tbname+'] enable trigger ALL' exec(@sql) fetch next from #tbc into @tbname end close #tbc deallocate #tbc go
drop procedure [dbo].[p_copydb]
GO
/*--数据库数据复制 将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制 适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 203.10--*//*--调用示例 exec p_copydb 'bns_aa','bns_new'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/
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
from '+@n_dbname+'..sysobjects where xtype=''U'' and status>=0'
exec(@sql)declare @tbname sysname
open #tbc
fetch next from #tbc into @tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table '+@n_dbname+'..['+@tbname+'] disable trigger ALL'
exec(@sql)
fetch next from #tbc into @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 @tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ALL'
exec(@sql)
set @sql='alter table '+@n_dbname+'..['+@tbname+'] enable trigger ALL'
exec(@sql)
fetch next from #tbc into @tbname
end
close #tbc
deallocate #tbc
go