Hi all,
hoping someone can help. I need to write some code to insert/append new data into data tables.
Basically our system refreshes daily and the current code drops the temp tables, recreates them and has to pull all the data again.
I'm wondering if there is a way that I can keep the temp tables and just insert/ or append new daily data?
Any help or advice would be greatly appreciated!
What you describe is like an ETL-process of a data warehouse. You can easily pull the latest data in a table, compare it with the designated table and delete, update and insert data so the data is in sync.
For example, when you have a dbo.Person table, you create for example stg.Person table. The stg schema is your staging schema. First you truncate the stg.Person table, then you load the new data into the stg.Person table. In the next step you compare the stg.data with the dbo.Person table. Syncing data can be done with the MERGE statement, however, seperated INSERT, UPDATE and DELETE statements are preffered.
You should be aware that when a Person is deleted in the source, how will you know if you have to delete it? Maybe you will find out that after all, dropping the temp tables, recreates them and pull all the data again isn't so bad after all.
You can do it in a stored procedure or with SQL Server Integration Services (SSIS) or Azure Data Factory (Cloud).