Sql Azure - Cross database queries

Question

I have N databases, for example 10 databases. Every database has the same schema, but different data. Now i would like to take every data of each database from the table "Table1" and insert them in a common table in a new database "DWHDatabase" in a table named Table1Common.

so it's an insert like n to 1. How i can do that? i'm trying to solve my issues with the elastic queries but seems it's a 1 to 1 stuff

Answer 1

Use Azure Data Factory with Linked Services to each database. Use the Copy activity to load the data.

You can also paramaterize the solution.

• Parameterize linked services
• Parameters in Azure Data Factory by Catherine Wilhemsen

Answer 2

Elastic query is best suited for reporting scenarios in which the majority of the processing (filtering, aggregation) may be done on the external source side. It is unsuitable for ETL procedures involving significant amounts of data transfer from a distant database (s). Consider Azure Synapse Analytics for large reporting workloads or data warehousing applications with more sophisticated queries.

You may use the Copy activity to copy data across on-premises and cloud-based data storage. After you've copied the data, you may use other actions to alter and analyse it. The Copy activity may also be used to publish transformation and analysis findings for use in business intelligence (BI) and application consumption.

MSFT Copy Activity Overview: Here.