SQL cross-database replication table data

Data operations between different server databases

Ways to replicate table data between different databases:

When the table target table is present:

INSERT INTO destination database: Table SELECT * FROM source database: Table

When the target table does not exist:

SELECT * Into destination database: Table from source database: Table

--If between different sql:

INSERT INTO OPENROWSET (' SQLOLEDB ', ' destination server name '; Sa '; ', destination database. dbo. Table)
SELECT * FROM source database: Table

--Create a linked server
exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' 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

--Remove linked server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

--Connect 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)

--Raw cost surface
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
Select *from Local surface

--Update the local surface
Update b
Set B. Column a=a. Column A
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

--openquery usage requires creating a connection

--First create a connection to create a linked server
exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
--Query
SELECT *
From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ')
--Import local tables to remote tables
Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')
SELECT * FROM local surface
--Update the local surface
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 a=b. Column A

--3, Opendatasource/openrowset
SELECT *
From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta
--Import local tables to remote tables
Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name
SELECT * FROM

--------------------------------------------------------------------------------------------------------------- --------------------

Well, if it bothers me, here's an easier-to-understand example:

Exec sp_droplinkedsrvlogin Dbvip,null
Exec sp_dropserver DBVIP

EXEC sp_addlinkedserver
@server = ' DBVIP ',--the server alias being accessed
@srvproduct = ' ',
@provider = ' SQLOLEDB ',
@datasrc = ' Server2 '--the server to be accessed

EXEC sp_addlinkedsrvlogin
' Dbvip ',--the server alias being accessed
' False ',
Null
' Sa ',--account number
' Thankyoubobby '--Password

Select * from DBVIP.pubs.dbo.orders


oracle////////////////////////////
Exec sp_droplinkedsrvlogin Demo,null
Exec sp_dropserver Demo
Go

EXEC sp_addlinkedserver
@server = ' demo ',
@srvproduct = ' Oracle ',
@provider = ' Msdaora ',
@datasrc = ' ServiceName '

EXEC sp_addlinkedsrvlogin
' Demo ',
' False ',
Null
' UserID ',
' Password '
Go

SELECT * FROM OPENQUERY (demo, ' select * from Tbdemo ')

UPDATE OPENQUERY (demo, ' SELECT ID from tbdemo WHERE id = 101 ')
SET name = ' Hello ';

INSERT OPENQUERY (demo, ' SELECT ID from Tbdemo ')
VALUES (' Hello ');

DELETE OPENQUERY (demo, ' SELECT ID from tbdemo WHERE name = ' Hello ');

-------------------------------------------Specific Examples------------------------------------------------------------

if exists (select 1 from master.dbo.sysservers where srvname = ' test ')
Begin
exec sp_droplinkedsrvlogin ' test ', ' sa '
exec sp_dropserver ' test '
End

--Establish a connection server
EXEC sp_addlinkedserver ' test ', ' Ms ', ' SQLOLEDB ', ' 192.168.1.99 '

EXEC sp_addlinkedsrvlogin ' test ', ' false ', NULL, ' sa ', '
SELECT * FROM Test.db_film.dbo.T_film
Go

if exists (select 1 from master.dbo.sysservers where srvname = ' test ')
Begin
exec sp_droplinkedsrvlogin ' test ', ' sa '
exec sp_dropserver ' test '
End

Go

SQL cross-database replication table data