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 ).