SQL Server cross-Library access

MSSQLSERVER data operations between different server databases

Method 1:

--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 '

Method 2:

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

Method 3:

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

Method 4:

--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 local surface

/************** Self-finishing ****************/
/* Establish remote database connection */exec sp_addlinkedserver ' ITSV ',//Remote connection name ', ' SQLOLEDB ', ' 192.168.200.3\sqlexpress ';//server to access//login to database server * /exec sp_addlinkedsrvlogin ' ITSV ',//remote connection name ' false ', NULL, ' sa ',//remote database login ' sa123 ';//remote database login password//test cross-Library query execution */select * From
ITSV. DBVideo.dbo.TD_Camera_LIST; [Remote connection name]. [Database name].dbo. [Target table name under this database]/* delete remote database connection */exec sp_droplinkedsrvlogin ' itsv ', NULL; EXEC sp_dropserver ' ITSV '; EXEC sp_dropserver ' itsv ', ' droplogins ';/* View existing Connection entity classes */sp_helpserver SELECT * from Sys.sysservers

Report:

Example of function of stored procedure name/view name

sp_addlinkedserver registering a remote DB instance exec sp_addlinkedserver ' instancename '

sp_dropserver Deleting a remote DB instance exec sp_dropserver ' instancename '

sp_addlinkedsrvlogin registered Remote Instance login access account exec sp_addlinkedsrvlogin ' instancename ', NULL

Sp_droplinkedsrvlogin Delete Remote instance login access account EXEC sp_droplinkedsrvlogin ' instancename ', ' UserName '

Sp_helpserver an accessible instance of the current instance that is registered (that is, viewing an instance that has been registered with sp_addlinkedserver) sp_helpserver

sys.sysservers function with Sp_helpserver select * from Sys.sysservers

Sys.linked_logins viewing registered login access accounts (that is, viewing accounts registered with sp_addlinkedsrvlogin) SELECT * FROM Sys.linked_logins

Sys.remote_logins view registered Remote access account SELECT * from Sys.remote_logins