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
Execsp_addlinkedserver‘Itsv‘,‘‘,‘SQLOLEDB‘,‘Remote server name or IP address‘
Execsp_addlinkedsrvlogin‘Itsv‘,‘False‘,Null,‘User name‘,‘Password‘

--query Example
Select*FromITSV. Database name. dbo. Table name

--Import sample
Select *IntoTableFromITSV. Database name. dbo. Table name

--Remove linked server when no longer in use
ExecSp_dropserver‘Itsv‘,‘Droplogins‘

--Connecting Remote/LAN data (Openrowset/openquery/opendatasource)
--1, OpenRowset

--query Example
Select*FromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)

--Raw cost surface
Select*IntoTableFromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)

--Importing a local table to a remote table
InsertOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)
Select*FromLocal surface

--Update local surface
UpdateB
SetB. Column A=A. Column A
FromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)AsAinner join  Local Table B on a.column1=b.column1 --openquery usage need to create a connection  --first create a connection 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 the local table into the 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 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