I have a partitioned table in SQL server 2017. I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table.
I'm using the following query :
ALTER TABLE original_table SWITCH PARTITION 2 TO staging_table PARTITION 2
Normally this query finishes in no time (the partition contains ~ 12M records).
the issue is when there is at the same time a query which is inserting data to original_table , knowing that the new inserted data resides in a different partition that I'm trying to switch. In this case , when I run the alter table query , it is locked by the insert query.
How to manage this issue ?