Drop large partition and recreate

Question

I have a large fact table that contains one partition by year and month (yyyymm), I need to delete all data in a month and I think to delete and recreate the partition to make my work easier. My table has +200kk of rows and drops partition makes more sense.

I read this Microsoft's article Load new data into partitions that contain data in one step but is not clear how to drop a partition and recreate another empty partition to load the new data.

Is there a strategy to do this?

Answer 1

Following the process we use in this case:

• we identify the range of the partition we want to change
• next we perform a CREAT TABLE ... AS SELECT... with the data we want to load into the partition. This table has to be created with exactly the same structure as the destination table and should have the same partition range as the partition you want to change
• Additionally we create a new table with the same structure and partition range as the desired partition, BUT WITHOUT any data
• In the following steps we perform a partition switch from the destination table into the empty table and afterwards a partition switch from the table we created per CTAS into the destination table.
• last but not least we drop the two "single partition" tables (the one which contained the data we wanted to switch in and the former empty table which now contains the data we wanted to switch out).