SQLSERVER: Data operations between databases on different servers

Data operations between databases on different servers -- create a connection server execsp_add1_serveritsv, SQLOLEDB, remote server name or IP address execsp_add1_srvloginitsv, false, null, user name, password -- Query example select * fromITSV. Database Name. Dbo. Table

Data operations between databases on different servers-create a link to the server exec sp_add+server 'itsv', '', 'sqlodb', 'remote server name or IP address 'exec sp_add+srvlogin 'itsv ', 'false', null, 'username', 'Password' -- Query example select * from ITSV. database Name. Dbo. Table

Data operations between databases on different servers

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin 'itsv', 'false', null, 'username', 'Password'

-- Query example

Select * from ITSV. Database Name. Dbo. Table Name

-- Import example

Select * into table from ITSV. Database Name. Dbo. Table Name

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)

-- 1. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name', 'username', 'Password', and database name. Dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. Dbo. Table name)

-- Import a local table to a remote table

Insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. Dbo. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = a. Column

From openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. Dbo. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. Dbo. Table name ')

-- Import a local table to a remote table

Insert openquery (ITSV, 'select * FROM database. Dbo. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = a. Column B

FROM openquery (ITSV, 'select * FROM database. Dbo. Table name') as

Inner join local table B on a. Column A = B. Column

-- 3. opendatasource/openrowset

SELECT *

FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Test. dbo. roy_ta

-- Import a local table to a remote table

Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Database. Dbo. Table Name

Select * from local table

1. remote replication

Copy the local data table to a remote table.

SELECT * INTO opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Database. Dbo. Table Name

FROM [local table]

This method cannot be used.

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

Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Database. Dbo. Table Name

Select * from local table

.

Note: primary key and auto increment problems. Turn off the auto increment switch first.

2. UPDATE Remote Server table

UPDATE a SET

A. [column name] = B. [column name]

FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Database. Dbo. Table name AS

Inner join [local table]

ON a. [key name] = B. [key name]

Note: The table name used after UPDATE is an alias of FROM, which is very important !!!

If the table name after UPDATE (at Blue a) uses the remote link table name (in opendatasource mode), the (Remote table) field to be replaced is replaced, replace with the content of the first field in the local table (all rows are replaced with one value ).

,