MSSQL data operations between different Server databases [posting]

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