How to optimize data aggregation from multiple MSSQL databases?

Question

SQL Server

Good afternoon.
There are several instances of MSSQL2008 databases that are identical in structure.
The essence of the task is to combine data under one instance.
I still mastered a few small ones, exporting the corresponding table from each instance to Excel, manually reducing everything into one file, editing keys, etc.
But now the volume of data has increased, tables that were previously empty are filled, and so on. The databases work on production, while I'm doing a manual routine, data is being added (a couple of dozen lines a day), it's hard to keep everything and not lose it.
In total, about 30 tables are used.
Is it possible to somehow optimize this process, visualize, simplify, etc.? I understand that a lot depends on the specific database, but still, I feel there should be something better than Excel.

Answer 1

Well sql among themselves quite are able to be on friendly terms - linked server. And the data - here only from understanding of the circuit.
As a result, you will get a certain script that will, in a simple case, do insert into dst select ... from src and then delete from src, and in more complex situations it will be a little more poignant.
But it's real ... albeit a chore even 'living' in a more or less large base scheme

Answer 2

Google towards replication