SQL Server commonly uses cross-database queries and SQL Server Cross-database queries

SQL Server commonly uses cross-database queries and SQL Server Cross-database queries

1. Temporary use OpenRowset

A. Use OPENROWSET with SELECT and Microsoft ole db Provider for SQL Server
The following example uses Microsoft ole db Provider for SQL Server to access the authors table of the pubs database on a remote data Server named remote1. Initialize the provider from datasource, user_id, and password, and use SELECT to query the data table.

SELECT * from openrowset ('sqloledb', 'remote1'; 'sa'; 'mypass', [pubs]. dbo. authors) AS

B. Use OPENROWSET and OLE DB Provider for ODBC
The following example uses the ole db Provider for ODBC and SQL Server ODBC drivers to access the authors table of the pubs database on a remote data Server named remote1. Use the ODBC syntax used by the ODBC provider to specify provider_string to initialize the provider, and use the catalog. schema. object syntax to define the returned database table.

Select * from openrowset ('msdasql ', 'driver = {SQL Server}; SERVER = remote1; UID = sa; PWD = mypass', [pubs]. dbo. pubs) As

2. If frequent access or large data volume occurs, we recommend that you use a linked server.

-- Create a linked server
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqlodb', 'remote server name or IP address'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'username', 'Password'

-- Query example
Select * from srv_lnk. Database Name. dbo. Table Name

-- Import example
Select * into table from srv_lnk. Database Name. dbo. Table Name

-- Delete the linked server after processing.
Exec sp_dropserver 'srv _ lnk ', 'droplogins'

Zookeeper