https://www.heckjj.com/index.php zh-cn https://www.heckjj.com/post// Heck <@hecks.tk> Wed, 31 Aug 2011 14:22:52 +0000 https://www.heckjj.com/post// 在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

--关闭用户进程处理 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包。

--如果两个库的结构有些不同,就用: /*--数据库数据复制,将一个数据库中的数据复制到另一个数据库 --如果某列在目标数据库中为标识列,将不会被复制 --适用范围:数据库结构发生了变化,想将旧数据库进行升级 --这样就可以根据新的数据库结构创建一个空库,然后 --将旧数据库的所有数据复制到新库中 --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
Tags - 数据库 , 复制数据 , sql ]]> https://www.heckjj.com/post//#blogcomment <user@domain.com> Thu, 01 Jan 1970 00:00:00 +0000 https://www.heckjj.com/post//#blogcomment