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

qco9c6ql 于 2023-11-16 发布在 SQL Server

关注(0)|答案(1)|浏览(149)

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.

sql-server

来源：https://stackoverflow.com/questions/77443104/table-refresh-from-azure-sql-database-to-on-premise-sql-server-database

1条答案

按热度按时间

nfzehxib1#

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