不同数据库之间复制表的数据的方法

1、不同数据库之间复制表的数据的方法当表目标表存在时：insertinto目的数据库.表select*from源数据库.表当目标表不存在时：select*into目的数据库.表from源数据库.表-如果在不同的SQL之间：insertintoopenrowset（'sqloledb','目的服务器名''sa'",目的数据库.dbo.表）select*from源数据库.表-或用链接服务器：创建链接服务器execsp_addlinkedserver'srv_lnk',",'SQLOLEDB','

2、远程服务器名'execsp_addlinkedsrvlogin'srv_lnk','false',null,'sa','密码'execsp_serveroption'srv_lnk','rpcout','true'-这个允许调用链接服务器上的存储过程go-查询示例select*fromsrv_lnk.数据库名.dbo.表名-导入示例select*into表fromsrv_lnk.数据库名.dbo.表名go-后删除链接服务器execsp_dropserver'srv_l

3、nk','droplogins'-如果是将一个数据库中的数据全部复制到另一个数据库，而且两个库结构完全一样的话，就用备份/恢复的方式：/*-将一个数据库完整复制成另一个数据库-*/*-调用示例execp_CopyDbddbname='test'-*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'dbo.p_CopyDb')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropproceduredbo.p_CopyDbGOcreat

4、eprocp_CopyDbsdbnamesysname=",-定义要复制的数据库名，默认为当前数据库ddbnamesysname,-定义复制后生成的数据库名overexistbit=1,-是否覆盖已经存在的数据库killuserbit=1-是否关闭用户使用进程,仅overexist=l时有效asdeclaresqlvarchar(8000),bpathvarchar(8000),rpathvarchar(8000)-得到要复制的数据库名ifisnull(sdbname,")="setsdbname=db_name()-得到临时备份数据目录及文件名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'-生成数据库备份语句，进行数据库备份setsql='backupdatabasemailto:%20+sdbname+'tomailto:disk=%20%20%20+bpath+'''withNOINIT'exec(sql)-根据备份文件恢复成新的数据库(完成复制工作)setsql='restoredatabasemailto:%20+ddbname+'frommailto:disk=%20%20%20+bpath+&#

7、39;withfile=1'+casewhenoverexist=lthen',replace'else''end-得到数据库存放的默认目录-得到SQL安装时设置的数据文件路径selectrpath=rtrim(reverse(filename)frommaster.sysfileswherename='master'selectrpath=reverse(substring(rpath,charindex('',rpath),8000)-添加移动逻辑文件的处理-从备份文件中获取逻辑文件名declarelfnnvarcha

8、r(128),tpchar(1),iint-创建临时表，保存获取的信息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-关闭用户进程处理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-恢复数据库exec(sql)-删除备份的临时文件setsql='de

11、l"mailto:%20+bpath+%20"execmaster.xp_cmdshellsql,no_outputselectsql,bpath,rpathgo-如果一定要逐个表复制，用：use源库goexecsp_msforeachtable'select*into目标库.？from?'最好的办法是用DTS(导入导出工具)做好DTS包。-如果两个库的结构有些不同，就用：/*-数据库数据复制将一个数据库中的数据复制到另一个数据库如果某列在目标数据库中为标识列，将不会被复制适用范围:数据库结构发生了变化，想将旧数据库进行升级这样就可以根据新的数据库结构创建一

12、个空库，然后将旧数据库的所有数据复制到新库中-*/*-调用示例execp_copydb'源数据库','目标数据库'execp_copydb'acc_五医','acc_演示数据8'-*/ifexists(select*fromdbo.sysobjectswhereid=object_id(N'dbo.p_copydb')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropproceduredbo.p_copydbGOcreateprocp_copydbo_dbname

13、sysname,-要复制数据的数据库-源数据库n_dbnamesysname,-接收数据的数据库-目标数据库cleardbbit=0-清空目标数据库asdeclaresqlnvarchar(4000)-禁用约束，防止复制时的数据冲突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-复制数据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#