Remote database connection

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 local table