Actual operations SQL server connections query data of different server tables

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 one server, import local Excel2. query the data of another server, and import local Excel3.Excle into the database. The database comes with the Excel import database function 4. connection query, O

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 it to a local Excel file. query the data of another server and import it to the local Excel file. import Excle to the database. The database comes with the Excel import function. connection query, O

Today, the product department needs to export batch data, but several tables that need to be connected for query and query are not the same. 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. Excle import, which comes with the Excel Database Import 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.

2. Query

Select A. ProjCode As ' Project No. ', B. projname as 'house name', count (1) as 'house quantity 'from openrowset ('sqlodb', 'IP address (server name)'; 'username '; 'Password ', [database name]. [dbo]. [Table name]) a, openrowset ('sqlodb', 'IP address (server name) ', 'username', 'Password', [database name]. [dbo]. [Table name]) B where. projCode = B. newcode GROUP by. 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