两个不同数据库之间复制数据效率高的方法

为什么我们程序员如此难以

网页中通过协议实现调用e

8月31

大 | 中 | 小

22:22Heck

数据库 From: 本站原创

在MS-SQLServer中，如何在不同的数据库之间复制表？
当表目标表存在时：
insert into 目的数据库..表(字段1...字段n) select 段1...字段n from 源数据库..表

当目标表不存在时：
select 段1...字段n into 目的数据库..表 from 源数据库..表
--如果在不同的SQL之间:
insert into openrowset('sqloledb','目的服务器名';'sa';'',目的数据库.dbo.表)
select * from 源数据库..表

除了上面的方法之外或者还可以用链接服务器:

--创建链接服务器 
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','远程服务器名' 
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','密码' 
exec sp_serveroption 'srv_lnk','rpc out','true' --这个允许调用链接服务器上的存储过程 
go

--查询示例 
select * from srv_lnk.数据库名.dbo.表名

--导入示例 
select * into 表 from srv_lnk.数据库名.dbo.表名

go 
--后删除链接服务器 
exec sp_dropserver 'srv_lnk','droplogins'

--如果是将一个数据库中的数据全部复制到另一个数据库,而且两个库结构完全一样的话,就用备份/恢复的方式:

/*--将一个数据库完整复制成另一个数据库

--Heck--*/

/*--调用示例 
exec p_CopyDb @ddbname='test' 
--*/

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 
@sdbname sysname='', --定义要复制的数据库名,默认为当前数据库 
@ddbname sysname, --定义复制后生成的数据库名 
@overexist bit=1, --是否覆盖已经存在的数据库 
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效 
as 
declare @sql varchar(8000),@bpath varchar(8000),@rpath varchar(8000)

--得到要复制的数据库名 
if isnull(@sdbname,'')='' set @sdbname=db_name()

--得到临时备份数据目录及文件名 
select @bpath=rtrim(reverse(filename)) from master..sysfiles where name='master' 
select @bpath=substring(@bpath,charindex('\',@bpath)+1,8000) 
,@bpath=reverse(substring(@bpath,charindex('\',@bpath),8000))+'BACKUP\' 
+@sdbname+'_'+convert(varchar,getdate(),112) 
+'_'+replace(convert(varchar,getdate(),108),':','') 
+'.bak'

--生成数据库备份语句,进行数据库备份 
set @sql='backup database '+@sdbname 
+' to disk='''+@bpath 
+''' with NOINIT' 
exec(@sql)

--根据备份文件恢复成新的数据库(完成复制工作) 
set @sql='restore database '+@ddbname 
+' from disk='''+@bpath+'''' 
+' with file=1' 
+case when @overexist=1 then ',replace' else '' end

--得到数据库存放的默认目录 
--得到SQL安装时设置的数据文件路径 
select @rpath=rtrim(reverse(filename)) from master..sysfiles where name='master' 
select @rpath=reverse(substring(@rpath,charindex('\',@rpath),8000))

--添加移动逻辑文件的处理 
--从备份文件中获取逻辑文件名 
declare @lfn nvarchar(128),@tp char(1),@i int

--创建临时表,保存获取的信息 
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),
sz numeric(20,0),Msz numeric(20,0)) 
--从备份文件中获取信息 
insert into #tb exec('restore filelistonly from disk='''+@bpath+'''') 
declare #f cursor for select ln,tp from #tb 
open #f 
fetch next from #f into @lfn,@tp 
set @i=0 
while @@fetch_status=0 
begin 
select @sql=@sql+',move '''+@lfn+''' to '''+@rpath+@ddbname+cast(@i as varchar) 
+case @tp when 'D' then '.mdf''' else '.ldf''' end 
,@i=@i+1 
fetch next from #f into @lfn,@tp 
end 
close #f 
deallocate #f

--如果一定要逐个表复制用:
use 源库
go
exec sp_msforeachtable
select * into 目的数据库..from..

最好的办法是用DTS(导入导出工具)做好DTS包。

--如果两个库的结构有些不同,就用: 
/*--数据库数据复制,将一个数据库中的数据复制到另一个数据库 
--如果某列在目标数据库中为标识列,将不会被复制 
--适用范围:数据库结构发生了变化,想将旧数据库进行升级 
--这样就可以根据新的数据库结构创建一个空库,然后 
--将旧数据库的所有数据复制到新库中 
--Heck--*/

/*--调用示例 
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 '+@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

来源：Heck's Blog
地址：https://www.heckjj.com/post/361/
转载时须以链接形式注明作者和原始出处及本声明，否则将追究法律责任，谢谢配合！

阅读(10898) | 评论(0) | 引用(0)

< 2026 > < 4 >
日	一	二	三	四	五	六
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30

Heck's Blog

两个不同数据库之间复制数据效率高的方法

关于博主

分类

日历

统计

最新日志

其他

选择模板

链接

最新评论

赞助商链接

归档