Copying a missing table for UAT Azure SQL Database from Production Azure SQL Database

Question

Hi Geeks, I need to copy a missing table from my prod azure sql database to UAT Azure sql database. I cannot overwrite or refresh as it has various other UAT test data by different team. Since it's not straight forward like in on premises server. I need a way to do it. Please suggest.

I tried below but it's not working

USE [target_database];
SELECT * INTO [target_schema].[table_name]
FROM [source_server].[source_database].[source_schema].[table_name];

Answer 1

Hey,
unlike on prem databases, you cannot directly cross query between 2 Azure SQL databases.

for you to cross query, you would need to use elastic query concept by creating external table .

But the best way to sync data for a table would be to leverage Azure data factory pipeline :
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database?tabs=data-factory

since in case of large datasets, ADF would be a quick syn unlike External table wherein you might get performance issues