Data replication between different SQL Server servers

Data operations between databases on different servers

-- Create linked server
Exec Sp_addmediaserver ' Itsv ' , ' ' , ' Sqloledb ' , ' Remote Server name or IP address '
Exec Sp_add1_srvlogin ' Itsv ' , ' False ' , Null , ' User Name ' , ' Password '

--Query example
Select * FromItsv. Database Name. DBO. Table Name

-- Import example
select * into table from itsv. database Name. DBO. table name

--Delete linked servers when they are no longer in use
ExecSp_dropserver'Itsv','Droplogins'

-- Connect to remote/LAN data (OpenRowSet/openquery/OpenDataSource)
-- 1. OpenRowSet

-- Query example
Select * From OpenRowSet ( ' Sqloledb ' , ' SQL Server Name ' ; ' User Name ' ; ' Password ' , Database name. DBO. Table name)

-- Generate local table
Select * Into Table From OpenRowSet ( ' Sqloledb ' , ' SQL Server Name ' ; ' User Name ' ; ' Password ' , Database name. DBO. Table name)

-- Import a local table to a remote table
Insert OpenRowSet ( ' Sqloledb ' , ' SQL Server Name ' ; ' User Name ' ; ' Password ' , Database name. DBO. Table name)
Select * From Local table

-- Update local table
Update B
Set B. Column = A. Column
From OpenRowSet ( ' Sqloledb ' , ' SQL Server Name ' ; ' User Name ' ; ' Password ' , Database name. DBO. Table name) As A Inner Join Local Table B
On A. column1 = B. column1

-- To use openquery, you must create a connection.

-- First create a connection to create a linked server
Exec Sp_addmediaserver ' 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 A
Inner Join Local Table B On A. Column = B. Column

-- 3. OpenDataSource/OpenRowSet
Select *
From OpenDataSource ( ' Sqloledb ' , ' Data Source = IP/servername; user id = login name; Password = Password ' ). Test. DBO. roy_ta
-- Import a local table to a remote table
Insert OpenDataSource ( ' Sqloledb ' , ' Data Source = IP/servername; user id = login name; Password = Password ' ). Database. DBO. Table Name
Select * From