Fastest mechanism to move data between tables in SQL server

Question

We are building a data warehouse. For some of our target tables, the only way to manage the process correctly is to re-transform all the data every time something changes.

Currently we do this using a 'table swap', ie:

• Create new data in temp schema 'B'
• Open transaction
• Move A.Table to schema C
• Move B.Table to schema A
• Move C.Table to schema B
• Commit

In theory this is all instant, because SQL never needs to physically copy any data. However due to the schema locks that get acquired, we are getting terrible concurrency.

Is there any other way of bulk moving data between tables (move, not copy), or do we have to fall back to doing an INSERT INTO (which might be bulk logged, but will still cause all the data to be written to disk again, which is slow)

The tables in question are several GB, hence the performance issues

SQL 2012

Answer 1

The better way wqould be to fix the broken programming requiring a total re-transform of all data. THat is a terrible non-scaling approach to start with and whoever justified that - I hope they now work in another field, as far away from any project responsibility as possible.

If you use enterprise edition you could possibly use a partition and partition swaps - even with only ONE Partition that at least has some schema lock optimizations. The schema of the table never changes. It has negative implications on statistics, though, before SQL Server 2014.

At the end it really runs down to a non-competent approach that was choosen - those "smart shortcuts" always come back to bite you.