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

asked 2022-12-28T09:12:35.333+00:00
Allen Francis 26 Reputation points

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];

Azure SQL Database
{count} votes

Accepted answer
  1. answered 2022-12-28T10:09:29.357+00:00
    Nandan Hegde 21,606 Reputation points

    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


0 additional answers

Sort by: Most helpful