FootyRef (6/9/2015)
I want to bring that partition month into its own staging table, do the inserts and updates and then switch it back to the original fact table from the staging table.
Why?
What are you going to gain from doing all that work as opposed to just doing the inserts and updates on the fact table?
It's not about preventing locks, you said no one's reading the table.
If the indexing on the fact table supports the updates then it's not about performance (and if they don't, then you'll need to add extra steps to add indexes to the staging table after the switch out and drop the indexes before switching back in)
So what do you expect to gain?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability