[SQL Server] data operations between databases on different servers

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

 

 

 

My instances

Create Table my (ID int, name varchar (20 ))

Create proc proc_my
As
Insert into my select convert (INT, getdate (), getdate ()

Insert OpenDataSource ('sqlodb', 'Data source = 192.168.102.208; user id = sa; Password = Sa'). master. DBO. My
Select convert (INT, getdate (), getdate ()

Exec proc_my

Select * from my order by newid ()
Select * From OpenDataSource ('sqloledb', 'Data source = 192.168.102.208; user id = sa; Password = Sa'). master. DBO. My