不同數(shù)據(jù)庫(kù)之間復(fù)制表的數(shù)據(jù)的方法

1、不同數(shù)據(jù)庫(kù)之間復(fù)制表的數(shù)據(jù)的方法當(dāng)表目標(biāo)表存在時(shí)：insertinto目的數(shù)據(jù)庫(kù).表select*from源數(shù)據(jù)庫(kù).表當(dāng)目標(biāo)表不存在時(shí)：select*into目的數(shù)據(jù)庫(kù).表from源數(shù)據(jù)庫(kù).表-如果在不同的SQL之間：insertintoopenrowset（'sqloledb','目的服務(wù)器名''sa'",目的數(shù)據(jù)庫(kù).dbo.表）select*from源數(shù)據(jù)庫(kù).表-或用鏈接服務(wù)器：創(chuàng)建鏈接服務(wù)器execsp_addlinkedserver'srv_lnk',",'SQLOLEDB','

2、遠(yuǎn)程服務(wù)器名'execsp_addlinkedsrvlogin'srv_lnk','false',null,'sa','密碼'execsp_serveroption'srv_lnk','rpcout','true'-這個(gè)允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過(guò)程go-查詢示例select*fromsrv_lnk.數(shù)據(jù)庫(kù)名.dbo.表名-導(dǎo)入示例select*into表fromsrv_lnk.數(shù)據(jù)庫(kù)名.dbo.表名go-后刪除鏈接服務(wù)器execsp_dropserver'srv_l

3、nk','droplogins'-如果是將一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)全部復(fù)制到另一個(gè)數(shù)據(jù)庫(kù)，而且兩個(gè)庫(kù)結(jié)構(gòu)完全一樣的話，就用備份/恢復(fù)的方式：/*-將一個(gè)數(shù)據(jù)庫(kù)完整復(fù)制成另一個(gè)數(shù)據(jù)庫(kù)-*/*-調(diào)用示例execp_CopyDbddbname='test'-*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'dbo.p_CopyDb')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropproceduredbo.p_CopyDbGOcreat

4、eprocp_CopyDbsdbnamesysname=",-定義要復(fù)制的數(shù)據(jù)庫(kù)名，默認(rèn)為當(dāng)前數(shù)據(jù)庫(kù)ddbnamesysname,-定義復(fù)制后生成的數(shù)據(jù)庫(kù)名overexistbit=1,-是否覆蓋已經(jīng)存在的數(shù)據(jù)庫(kù)killuserbit=1-是否關(guān)閉用戶使用進(jìn)程,僅overexist=l時(shí)有效asdeclaresqlvarchar(8000),bpathvarchar(8000),rpathvarchar(8000)-得到要復(fù)制的數(shù)據(jù)庫(kù)名ifisnull(sdbname,")="setsdbname=db_name()-得到臨時(shí)備份數(shù)據(jù)目錄及文件名selectbpa

5、th=rtrim(reverse(filename)frommaster.sysfileswherename='master'selectbpath=substring(bpath,charindex('',bpath)+l,8000),bpath=reverse(substring(bpath,charindex('',bpath),8000)+'BACKUP'mailto:+sdbname+%20_%20+convert(varchar,getdate(),112)+'_'+replace(convert(va

6、rchar,getdate(),108),':',")+'.bak'-生成數(shù)據(jù)庫(kù)備份語(yǔ)句，進(jìn)行數(shù)據(jù)庫(kù)備份setsql='backupdatabasemailto:%20+sdbname+'tomailto:disk=%20%20%20+bpath+'''withNOINIT'exec(sql)-根據(jù)備份文件恢復(fù)成新的數(shù)據(jù)庫(kù)(完成復(fù)制工作)setsql='restoredatabasemailto:%20+ddbname+'frommailto:disk=%20%20%20+bpath+&#

7、39;withfile=1'+casewhenoverexist=lthen',replace'else''end-得到數(shù)據(jù)庫(kù)存放的默認(rèn)目錄-得到SQL安裝時(shí)設(shè)置的數(shù)據(jù)文件路徑selectrpath=rtrim(reverse(filename)frommaster.sysfileswherename='master'selectrpath=reverse(substring(rpath,charindex('',rpath),8000)-添加移動(dòng)邏輯文件的處理-從備份文件中獲取邏輯文件名declarelfnnvarcha

8、r(128),tpchar(1),iint-創(chuàng)建臨時(shí)表，保存獲取的信息createtable#tb(lnnvarchar(128),pnnvarchar(260),tpchar(1),fgnnvarchar(128),sznumeric(20,0),Msznumeric(20,0)-從備份文件中獲取信息insertinto#tbexec('restorefilelistonlyfrommailto:disk=%20%20%20+bpath+)declare#fcursorforselectln,tpfrom#tbopen#ffetchnextfrom#fintolfn,tpseti=0

9、whilefetch_status=0beginselectsql=sql+',movemailto:%20%20%20+lfn+tomailto:%20%20%20+rpath+ddbname+cast(iasvarchar)+casetpwhen'D'then'.mdf'''else'.ldf'''end,i=i+1fetchnextfrom#fintolfn,tpendclose#fdeallocate#f-關(guān)閉用戶進(jìn)程處理ifoverexist=landkilluser=lbegindeclares

10、pidvarchar(20)declare#spidcursorforselectspid=cast(spidasvarchar(20)frommaster.sysprocesseswheredbid=db_id(ddbname)open#spidfetchnextfrom#spidintospidwhilefetch_status=0beginexec('killmailto:%20+spid)fetchnextfrom#spidintospidendclose#spiddeallocate#spidend-恢復(fù)數(shù)據(jù)庫(kù)exec(sql)-刪除備份的臨時(shí)文件setsql='de

11、l"mailto:%20+bpath+%20"execmaster.xp_cmdshellsql,no_outputselectsql,bpath,rpathgo-如果一定要逐個(gè)表復(fù)制，用：use源庫(kù)goexecsp_msforeachtable'select*into目標(biāo)庫(kù).？from?'最好的辦法是用DTS(導(dǎo)入導(dǎo)出工具)做好DTS包。-如果兩個(gè)庫(kù)的結(jié)構(gòu)有些不同，就用：/*-數(shù)據(jù)庫(kù)數(shù)據(jù)復(fù)制將一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)復(fù)制到另一個(gè)數(shù)據(jù)庫(kù)如果某列在目標(biāo)數(shù)據(jù)庫(kù)中為標(biāo)識(shí)列，將不會(huì)被復(fù)制適用范圍:數(shù)據(jù)庫(kù)結(jié)構(gòu)發(fā)生了變化，想將舊數(shù)據(jù)庫(kù)進(jìn)行升級(jí)這樣就可以根據(jù)新的數(shù)據(jù)庫(kù)結(jié)構(gòu)創(chuàng)建一

12、個(gè)空庫(kù)，然后將舊數(shù)據(jù)庫(kù)的所有數(shù)據(jù)復(fù)制到新庫(kù)中-*/*-調(diào)用示例execp_copydb'源數(shù)據(jù)庫(kù)','目標(biāo)數(shù)據(jù)庫(kù)'execp_copydb'acc_五醫(yī)','acc_演示數(shù)據(jù)8'-*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'dbo.p_copydb')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropproceduredbo.p_copydbGOcreateprocp_copydbo_dbname

13、sysname,-要復(fù)制數(shù)據(jù)的數(shù)據(jù)庫(kù)-源數(shù)據(jù)庫(kù)n_dbnamesysname,-接收數(shù)據(jù)的數(shù)據(jù)庫(kù)-目標(biāo)數(shù)據(jù)庫(kù)cleardbbit=0-清空目標(biāo)數(shù)據(jù)庫(kù)asdeclaresqlnvarchar(4000)-禁用約束，防止復(fù)制時(shí)的數(shù)據(jù)沖突setsql='declare#tbccursorforselectname,tbname=object_name(parent_obj)frommailto:%20+n_dbname+%20.sysobjectswherextypein("C","F")'exec(sql)declarenamesysname

14、,tbnamesysnameopen#tbcfetchnextfrom#tbcintoname,tbnamewhilefetch_status=Obeginsetsql='altertablemailto:%20+n_dbname+%20.%20+tbname+NOCHECKCONSTRAINT'+name+''exec(sql)fetchnextfrom#tbcintoname,tbnameendclose#tbc-復(fù)制數(shù)據(jù)declaresql1varchar(8000)setsql='declare#fr

15、om'mailto:+o_dbname+%20.sysobjectsainnerjoin'mailto:+n_dbname+%20.=wherea.xtype="U"andb.xtype="U"'exec(sql)open#tbfetchnextfrom#tbintotbnamewhilefetch_status=0beginselectsqll=",sql='selectsqll=sqll+","++丁from(selectnam

16、efrommailto:%20+o_dbname+%20.syscolumnswhereidin(selectidfrommailto:%20+o_dbname+%20.sysobjectswheremailto:name=%20%20%20+tbname+)ainnerjoin(selectnamefrommailto:%20+n_dbname+%20.syscolumnswherestatus<>0x80andidin(selectidfrommailto:%20+n_dbname+%20.sysobjectswheremailto:name=%20%20%20+tbname+

17、)='execsp_executesqlsql,N'sqllnvarchar(4000)out',sqlloutselectsql1=substring(sqll,2,8000)exec('insertintomailto:%20+n_dbname+%20.%20+tbname+%20(%20+sqll+')selectmailto:%20+sql1+frommailto:%20+o_dbname+%20.%20+tbname+%20)iferror<>0print('insertintomailto:%20+n_dbname+%20.%20+tbname+%20(%20+sqll+')selectmailto:%20+sql1+frommailto:%20+o_dbname+%20.%20+tbname+%20)fetchnextfrom#