Can I query perform SQL query by joining table 1 from (Prodution) database 1 to table 2 from (Production) databse 2? The result to be saved as a table in database 3 (Development)

Question

Perform AZURE SQL query by joining table 1 from (Azure Prodution) database 1 to table 2 from (Azure Production) database 2?
The result to be saved as a table in database 3 (Development)

Eg.

SELECT T1.CustomerID, T2.CustomerName
FROM database1.SalesTable AS T1
LEFT JOIN database2.CustomerTable AS T2

ON database1.SalesTable.CustomerID = database2.CustomerTable

Answer 1

Hey,
It is possible by couple of ways:

1) Create an external table in either of the Production database:
https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical

Then in Azure data factory use a copy activity to have your query as source and your sink as the table 3 in database dev.
https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server

But based on the amount of data there might be performance issues due to elastic query so we can go with the below approach :

2) use data flow to join both the data from table 1 and table 2 and copy into table 3