SQL Server cross-Library queries

Lele.xu-sql Server cross-Library query

Way One:

Statement

SELECT * from database a.dbo. Table A, database b.dbo. Table b b WHERE A.field=b.field

"DBO" can be omitted as

SELECT * from Database A. Table A, database B.. Table b b WHERE A.field=b.field

Mode two (the outer chain of another database is hung on one database):

SQL Server database:

--This is a mapping of a remote database

EXEC sp_addlinkedserver ' remote database IP or host name ', N ' SQL Server '

--This is a login to the remote database

EXEC sp_addlinkedsrvlogin ' remote database IP or hostname ', ' false ', NULL, ' login name ', ' password '

--After logging in, you can manipulate objects in the remote database in the following format

SELECT * FROM [the IP or host name of the remote database]. [Database name]. [dbo]. [Table name]

INSERT INTO OPENROWSET (' SQLOLEDB ', ' 192.168.0.100 '; Sa '; ' 10060 ', ' select * from Knss2009.dbo.yw_kck ') SELECT * from Yw_kck

Example:

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

Way three:

--Connect remote/LAN data (Openrowset/openquery/opendatasource)
--1, OPENROWSET (more recommend this approach)
--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

--2, OpenQuery usage need to create 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

Example of cross-Library fetch usage (pay attention to judgment):

IF EXISTS (SELECT 1 from sys.synonyms WHERE name= ' Syn305_ys_costandcashset ')

DROP synonym Syn305_ys_costandcashset

GO

CREATE synonym Syn305_ys_costandcashset for [dotnet_erp305_hnjy].dbo.ys_costandcashset

GO

Do not allow remote access exception resolution:

exec sp_configure ' show advanced options ', 1

Reconfigure

exec sp_configure ' Ad Hoc distributed Queries ', 1

Reconfigure

SQL Server cross-Library queries