How to get data by SQL query from multiple linked servers?

Question

SQL Server

There are about 40 SQL servers in the network, all of them are linked to one server. The servers differ only in the data set, the database structure is the same, the password for SA is the same for everyone.
It is necessary to collect data from all servers using an SQL script. An example script is below.

SELECT [Cashes].[CashName],
 CashDevices.[DeviceModel]
,[Cashes].Model 
,[Cashes].[RegistryNumber] 
,[Cashes].[DataChange] 
FROM [SES].[dbo].[CashDevices]
inner join  [SES].[dbo].[Cashes] Cashes ON [Cashes].ID=[CashDevices].[ID_Cash]
WHERE [DeviceType]='Фиск.память'

The problem is that I don’t know how to organize an automatic task in t-SQL to get data from all servers automatically into one table I need.
I ask you to at least help with advice in which direction to look for a solution.
Used MS SQL 2008

Answer 1

INSERT INTO "MainTable"
("ServerName", "CashName", "DeviceModel", "Model", "RegistryNumber", "DataChange")
SELECT
    -- Имя сервера-источника, чтобы не путаться
    'LinkedServer1' as "ServerName" 
    ,[Cashes].[CashName]
    ,[CashDevices].[DeviceModel]
    ,[Cashes].Model 
    ,[Cashes].[RegistryNumber] 
    ,[Cashes].[DataChange] 
FROM
    -- Указание источника <server>.<database>.<schema>.<table>
    [LinkedServer1].[SES].[dbo].[CashDevices] 
    inner join
        [LinkedServer1].[SES].[dbo].[Cashes] Cashes ON [Cashes].ID=[CashDevices].[ID_Cash]
WHERE [DeviceType]='Фиск.память'

Somehow it is possible. To make x40, write a procedure whose parameter will be the name LinkedServer.
As described
at technet.microsoft.com/en-us/library/ms175129%28v=s...
UNION is not supported.
But if it is still supported, you can make a View on each server that returns the result of the query. And write one big request on the main server (if there is enough memory, the request will be executed). This is bad style:

INSERT INTO "MainTable"
("ServerName", "CashName", "DeviceModel", "Model", "RegistryNumber", "DataChange")
SELECT "CashView".* FROM [LinkedServer1].[SES].[dbo].[CashView] "CashView"
UNION
SELECT "CashView".* FROM [LinkedServer2].[SES].[dbo].[CashView] "CashView"
UNION
SELECT "CashView".* FROM [LinkedServer3].[SES].[dbo].[CashView] "CashView"
UNION
SELECT "CashView".* FROM [LinkedServer4].[SES].[dbo].[CashView] "CashView"
...
UNION
SELECT "CashView".* FROM [LinkedServer40].[SES].[dbo].[CashView] "CashView"

- Determining data source by linked server
name technet.microsoft.com/en-us/library/ms190406%28v=s...
- Inserting rows using nested INSERT and SELECT
queries technet.microsoft.com/en-us/library/ ms189872%28v=s...
- Get names of all linked servers
support2.microsoft.com/kb/203638/en-us
According to the principle of least privilege, sa is redundant. On the linked servers, create accounts that will have access to a selection from the [SES].[dbo].[CashDevices] and [SES].[dbo].[Cashes] tables. Use these accounts to connect all 40 linked servers to the main server. Next is a matter of technology.