Statement
SELECT * FROM database A. dbo. Table A, database B. dbo. Table B B WHERE a. field = B. field
"DBO" can be omitted, as shown in
SELECT * FROM database A... table A, database B... table B B WHERE a. field = B. field
SqlServer database:
-- This statement maps to a remote database.
EXEC sp_addmediaserver 'IP address or Host Name of the remote database', n' SQL Server'
-- This statement is used to log on to the remote database.
EXEC sp_add1_srvlogin 'IP address or Host Name of the remote database ', 'false', NULL, 'login name', 'Password'
-- After logging on, You can operate objects in the remote database in the following format:
Select * from [IP address or Host Name of the remote database]. [database name]. [dbo]. [Table name]
Insert into openrowset ('sqloledb', '192. 168.0.100 '; 'sa'; '20170101', 'select * from knss2009.dbo. yw_kck ') select * from yw_kck
++ ++
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', 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