SQL Server local Query update operation code for remote database _mssql

Copy Code code as follows:

--pk
SELECT * from sys.key_constraints where object_id = object_id (' TB ')
--fk
SELECT * from Sys.foreign_keys where parent_object_id =object_id (' TB ')
--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 sample
SELECT * into table from ITSV. Database name. dbo. Table name

--Delete a linked server when you no longer use it
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 a local table into a remote table
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name)
Select *from Local surface

--Update the 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 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 a local table into a remote table
Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')
SELECT * FROM local surface
--Update the 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
---------------------------------------------------------------------------------------