-3

For full loads I stage the data for all tables, and then finally in a transaction perform a table switch to switch the staging and main tables.

For incremental load however, I stage last 7 days of data for all incremental load tables, and then in a transaction I perform an update/insert with the main table.

The incremental load is causing blocking and I was thinking of having a new table called holding table. I will stage the last 7 days of data, update the holding table and switch holding/main tables, and again update the holding table.

Is there any other approach?

Improve this question
3
  • 1
    Perhaps, based on the volume and type of questions you are asking, it is time to hire a consultant.
    – Thom A
    Commented 7 hours ago
  • How is such an unfriendly or unhelpful reply even allowed on this site. Not sure what's making you feel entitled to make this comment to me who is a learner seeking knowledge.
    – variable
    Commented 7 hours ago
  • Are you not using Snapshot/READ COMMITTED SNAPSHOT isolation? Loading data doesn't have to cause blocking.
    – David Browne - Microsoft
    Commented 6 hours ago
Related questions
771
What is the best way to auto-generate INSERT statements for a SQL Server table?
1385
Altering a column: null to not null
459
SELECT INTO a table variable in T-SQL
Load 4 more related questions Show fewer related questions

0

Reset to default

Browse other questions tagged or ask your own question.