SQL Server: Data manipulation between different server databases

Data operations between different server databases

--Create a linked server

exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example

SELECT * from ITSV. Database name. dbo. Table name

--Import sample

SELECT * into table from ITSV. Database name. dbo. Table name

--Delete a linked server when you no longer use it

exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OpenRowset

--query Example

SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

--Raw cost surface

SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

--Import a local table into a remote table

Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)

Select *from Local surface

--Update the surface

Update b

Set B. Column a=a. Column A

From OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B

On A.column1=b.column1

--openquery usage requires creating a connection

--First create a connection create a linked server

exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address '

--Query

SELECT *

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ')

--Import a local table into a remote table

Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')

SELECT * FROM local surface

--Update the surface

Update b

Set B. column b=a. Column B

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a

Inner JOIN local table B on a. Column a=b. Column A

--3, Opendatasource/openrowset

SELECT *

From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta

--Import a local table into a remote table

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Database. dbo. Table name

SELECT * FROM local surface

1. Remote Replication

Copy the local datasheet to a remote table, if you use the

SELECT * into OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Database. dbo. Table name

From [local surface]

There will be no error and the system is not allowed to use this method.

Therefore, you can only take a table that is the same as the local table in the remote database first, and then use the

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Database. dbo. Table name

SELECT * FROM local surface

for processing.

Note: The issue of primary key and self increment. To turn off the incremental switch first.

2. Update Remote server table

UPDATE a SET

a.[column name]=b.[name]

From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Database. dbo. Table name as a

INNER JOIN [Local surface]

On a.[key name]=b.[]

Note that the table name that is used after update is an alias to from, which is important!!!

If the table name (at Blue a) after the update uses the remote linked table name (OPENDATASOURCE), the replacement (remote table) field contents are replaced with the contents of the first field in the local table (all rows are replaced with a value).