不同資料庫之間複製表的資料的方法

不同資料庫之間複製表的資料的方法：

當表目標表存在時：

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/' 
mailto:+@sdbname+%20_%20+convert(varchar,getdate(),112) 
+'_'+replace(convert(varchar,getdate(),108),':','') 
+'.bak'

--產生Database Backup語句,進行Database Backup 
set @sql='backup database mailto:%20+@sdbname 
+' to mailto:disk=%20%20%20+@bpath 
+''' with NOINIT' 
exec(@sql)

--根據備份檔案恢複成新的資料庫(完成複製工作) 
set @sql='restore database mailto:%20+@ddbname 
+' from mailto:disk=%20%20%20+@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 mailto:disk=%20%20%20+@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 mailto:%20%20%20+@lfn+ to mailto:%20%20%20+@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 mailto:%20+@spid) 
fetch next from #spid into @spid 
end 
close #spid 
deallocate #spid 
end

--恢複資料庫 
exec(@sql)

--刪除備份的臨時檔案 
set @sql='del "mailto:%20+@bpath+%20%22 
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 mailto:%20+@n_dbname+%20..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 mailto:%20+@n_dbname+%20..[%20+@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 ' 
mailto:+@o_dbname+%20..sysobjects a inner join ' 
mailto:+@n_dbname+%20..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 mailto:%20+@o_dbname+%20..syscolumns where id in 
(select id from mailto:%20+@o_dbname+%20..sysobjects where mailto:name=%20%20%20+@tbname+) 
) a inner join ( 
select name from mailto:%20+@n_dbname+%20..syscolumns where status<>0x80 and id in 
(select id from mailto:%20+@n_dbname+%20..sysobjects where mailto:name=%20%20%20+@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 mailto:%20+@n_dbname+%20..[%20+@tbname+%20](%20+@sql1 
+') select mailto:%20+@sql1+ from mailto:%20+@o_dbname+%20..[%20+@tbname+%20]) 
if @@error<>0 
print('insert into mailto:%20+@n_dbname+%20..[%20+@tbname+%20](%20+@sql1 
+') select mailto:%20+@sql1+ from mailto:%20+@o_dbname+%20..[%20+@tbname+%20]) 
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 mailto:%20+@n_dbname+%20..[%20+@tbname+] CHECK CONSTRAINT ['+@name+']' 
exec(@sql) 
fetch next from #tbc into @name,@tbname 
end 
close #tbc 
deallocate #tbc 
go