Data transfer between different database servers

Method One:

1.select top 1000* from OpenDataSource (' SQLOLEDB ', ' Data source= database server IP address; User id= account; password= password '). Database name. dbo. data table

Example: Select Top 1000* from OpenDataSource (' SQLOLEDB ', ' Data source=59.188.1.140; User Id=sa; password=led123456 '). Pgenius.dbo.NEWS_MAIN

If an error is performed,

Cause: "SQL Server blocked access to component ' Ad Hoc distributed Queries ' STATEMENT ' Openrowset/opendatasource" when replicating data from a remote server to local Because this component has been shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ' ERROR

Execute the following code:

Enable AD Hoc distributed Queries:

exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure

Close AD Hoc distributed Queries:

exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure

Method Two:

2.

　　--1.0 Create a database link to a remote connection, which is an alias

EXEC sp_addlinkedserver ' ServerA ', ' ', ' SQLOLEDB ', ' Server IP address '

EXEC sp_addlinkedsrvlogin ' ServerA ', ' false ', NULL, ' sa ', ' sa account password '

Such as:

EXEC sp_addlinkedserver ' ServerA ', ', ' SQLOLEDB ', ' 127.1.0.1 '

EXEC sp_addlinkedsrvlogin ' ServerA ', ' false ', NULL, ' sa ', ' 123 '

　--2.0 Executing query statements

2.0.1: Direct Query

Select top 1* from [ServerA]. [Database name]. [dbo]. [Data table name]

2.0.2: Even table query

Select top 1* from [ServerA]. [Yao]. [dbo]. [News_main] as M inner join [ServerA]. [Yao]. [dbo]. [News_content] as C on m.id=c.id

2.0.3: Collecting data

--If the structure of table A and table B is the same,
INSERT into TableB SELECT * from ServerA.DatabaseA.dbo.TableA

--if the structure part of table A and table B is the same, then
INSERT into TableB (the column names that need to be copied, separated by commas) SELECT the list that needs to be copied, separated by commas from ServerA.DatabaseA.dbo.TableA

　　

Data transfer between different database servers