How to implement efficient cross-Server Query on SQL Server

BKJIA database channel recommends "SQL Server beginners to proficient" and "database index and query topics" to help you.

Today, the product department needs to export batch data, but several tables that need to be connected for query and query are not on the same server. So I started to do this:

1. query the data of a server and import the local Excel

2. query the data of another server and import the data to a local Excel file.

3. Import Excle into the database. The database comes with the Excel import database function.

4. Connection query, OVER!

Later, I learned that the product department needed data from more than 50 cities across the country, so I had to repeat it in every city.

This reminds me of cross-database queries.

1. Activate the Distributed Query permission

 
 

  
  
exec sp_configure 'show advanced options',1   
  
  
reconfigure  
  
  
exec sp_configure 'Ad Hoc Distributed Queries',1   
  
  
reconfigure 
 
 

2. Query

 
 

  
  
Select a. ProjCode as 'project No. ', B. projname as 'project name', count (1) as 'house quantity' from
  
  
Openrowset ('sqlodb', 'IP address server name) '; 'username'; 'Password', [database name]. [dbo]. [Table name])
  
  
, Openrowset ('sqlodb', 'IP address server name) '; 'username'; 'Password', [database name]. [dbo]. [Table name]) B
  
  
Where a. ProjCode = B. newcode GROUP by a. ProjCode, B. projname order by count (1) desc
 
 

3. Then change the city. You only need to change the IP address, database name, and table name.

4. Disable

 
 

  
  
exec sp_configure 'Ad Hoc Distributed Queries',0   
  
  
reconfigure   
  
  
exec sp_configure 'show advanced options',0   
  
  
reconfigure 
 
 

 

Original article title: SQL cross-Server Query

Link: http://www.cnblogs.com/zhanglei644213943/archive/2010/05/05/1727670.html

Edit recommendations]

1. SQL Server uses indexes to optimize data access
2. SQL Server database optimization experience
3. How to Use SQLServer database to query the cumulative value
4. Analysis of Oracle and SqlServer Stored Procedure debugging and error handling
5. SQL Server statements and stored procedures
6. 50 methods to optimize SQL Server database queries