sql-serverazure-sql-databaselinked-servertransactional-replicationazure-data-sync

Table refresh from Azure SQL database to on-premise SQL Server database

I have a similar requirement but the other way. I.e I have a table in an Azure SQL database, which we need to copy data from the Azure SQL database to an on-premise SQL Server database. The table refresh needs to be done once a day.

I am planning to create a linked server from the on-premise machine to Azure SQL, and then create a SQL Server Agent job to query the Azure SQL database from the on-premise server, download the data to .csv file, drop the existing table and re-create the table again and import the data from the .csv file.

Is this approach feasible? My table is very small and growth is very minimal. Please suggest if there is any easier way to do this task.

Solution

  • The approach you suggested is correct, but it can be lengthy one. instead of downloading data dropping table and again uploading data as @siggemannen suggested you cand directly insert the data with below script.

    --THIS QUERY WILL TRUNCATE THE DATA FROM THE TABLE(DELETE ALL THE DATA FROM THE TABLE)
TRUNCATE TABLE TABLE_NAME;

-- tHIS WILL INSERT THE DATA FROM LINKED SERVER TABLE TO ON PREM TABLE.
INSERT INTO sampletable 
SELECT * FROM [LINKED_SERVSER_NAME].[DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME];

    This will delete the records from table and insert the records from linked server table every time whenever SQL job will execute.

    SUCCESSFULL EXECUTION:

    enter image description here