Forum Discussion
Partitions - How to delete data and reload from and into a middle partition
We have a large data table that is partitioned by years ranging from 2016 through 2022. Due to a discrepancy found from the external entity, the existing 2020 data needs to be removed and reloaded. We only have the one partitioned table where we roll off a partition and add a new one as the yearly extracts are received. In our test environment, I used a delete to remove the rows where the year was 2020, and prior to the insert in my SSIS package, the column store index was disabled. However it has 120 million records to load, and it appears to be only at 86 mill after 40 hours. It takes a fourth of that time for a normal load into the latest partition. At this point I am just letting it run, however, I am looking for suggestion for a better approach. Can I target the insert directly into that partition from SSIS, and if not what is the best way to go about this load.
1 Reply
- In the end, one of my mistakes in the development environment that I corrected when I implemented in production was to use a DELETE to empty the partition instead of a TRUNCATE TABLE <name> WITH PARTITIONS (#). Truncate was the correct choice for the intended performance and amount data.
