We have a table 'Sales' which is Fully loaded daily. Our target delivery is insert else update, So every time most of the records will be updated which is taking huge time (> 1 hour).
We don't have a key in Source to make it incremental load.
Multiple Users are using that table from different Location, So we cant truncate and load, also We cant use 'Switch' Option in sql server.
We thought of the below approach.
1) Truncate and Load all the data in the 'Sales_Swap' table , This would be much better since it is just bulk insert.
Lock Table Sales
EXEC sp_rename 'Sales', 'Sales_temp';
EXEC sp_rename 'Sales_Swap', 'Sales';
EXEC sp_rename 'Sales_temp', 'Sales_Swap';
Experts, Please share your ideas whether the above option is good in ETL Prod environment
Thanks in Advance