SQL Server: Keep data inside a table equal to view output

Question

I have a view which joins data together from multiple tables on one SQL Server and i need to save the output of this view in a table on another SQL Server. The current approach is to use a SSIS Package and truncate the table every day and insert the output of the view into the table. But since the amount of data keeps growing and existing data changes, this approach takes way too long so i am searching for a better alternative.

Answer 1

You can use incremental load using SSIS which means comparing the target table against the source data based on Id or Date Stamp or Time Stamp.

Try to add 2 technical columns to your view :

InsertionDate : If there are any new records coming from the data source, then you have to insert those records in the target table and set the InsertionDate to UTCNOW().

UpdateDate : If there are any updated values in the data source, then we have to update those records in the target table, then you have to update those records in the target table and set the UpdateDate to UTCNOW().

SSIS components you can use :

Lookup : to understand the properties present in Lookup and Lookup Transformation in SSIS article to understand the configuration settings

Conditional Split Transformation : is just like the IF condition or CASE statement. It checks the given condition and based on the condition result; the output will send to the appropriate destination path. It has ONE input and MANY outputs.