Kenneth Fisher uses table partitioning to perform fast loads of data:
Now if this table is paritioned you’d use SWITCH and bring in a new partition.
For those that don’t know, when a table is partitioned, you can create a new empty partition, and a new empty table, load the table, make the table exactly match the partition (structure, check constraints, & indexes for example) and you can SWITCH it in. The SWITCH part is a metadata operation and is fast!
But what do you do if the table isn’t partitioned? Well, I was having a conversation with Andy Mallon (b/t) and he reminded me of something.
Read on for the details. The upshot is that you can take your time loading the second table and once you’re ready to swap out, it’s a quick metadata change. That’s really useful for ETL scenarios.