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
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