Heck's Blog

     今天碰到个问就是在不同数据库这间的表的数据的复制。嘿嘿，猪头我从来没有用过，所以在网上查了下，总结如下。
当表目标表存在时：
insert into 目的数据库..表 select * from 源数据库..表  
当目标表不存在时：
select * 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'

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

--将一个数据库完整复制成另一个数据库
/*--调用示例 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 --关闭用户进程处理 if @overexist=1 and @killuser=1 begin declare @spid varchar(20) declare #spid cursor for select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@ddbname) open #spid fetch next from #spid into @spid while @@fetch_status=0 begin exec('kill '+@spid) fetch next from #spid into @spid end close #spid deallocate #spid end --恢复数据库 exec(@sql) --删除备份的临时文件 set @sql='del "'+@bpath+'"' exec master..xp_cmdshell @sql,no_output select @sql,@bpath,@rpath go --如果一定要逐个表复制,用: use 源库 go exec sp_msforeachtable 'select * into 目标库..? from ?'

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


--如果两个库的结构有些不同,就用:

/*--数据库数据复制

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

适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--*/
/*--调用示例 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/10/
转载时须以链接形式注明作者和原始出处及本声明，否则将追究法律责任，谢谢配合！