Error encountered while alter-switch the partition table _ database Other

Create a table as defined as the target table;
Add constraint to the table to ensure that the data in the table is in the target partition;
Alter table source table switch to target table partition partition code
Where the partition number can be obtained by $partition.partion_func (column value)
This process is much faster than insert select because it does not involve IO, it only needs to modify the metadata, the side of the partition Onwer.

However, the above procedure applies only to situations where the target partition already exists in the partition function of the destination table. For example, if you define a partition function with a boundary of 1,2,3, then 4 and 5 are inserted into the 4th partition.

So I recommend using this method of defining all partitions of the partitioning function in advance, and if you want to partition by day, you define 1000 partitions first. This method is simpler and does not require checking when data is added, resulting in errors.

If you are sure you want to use dynamic partitioning, when you add a source table as a new partition, you need to do the following:

First gets the value of the newly added partition column, set to X;
Through the Sys. Partition_range_values and Sys.partition_functions Find all the boundary values of the target partition function;
Check for the following:
Here the demarcation value belongs to the left partition or the right partition is different, we assume belongs to the left partition;
If x exists in the 2-Step boundary value collection, congratulations, no extra action is required.
If x is greater than the maximum boundary in step 2, you need not only split a partition for x, but also ensure that data smaller than x has its own partition.
Each time you join the partition, check for existing boundary values, if not equal to any of them, then split with X and delete the same data in the target table for the partition column and X.
Switch the source table to the target partition as described previously.
If the target partition already has data in 2 steps, alter ... the switch statement fails because the destination partition is not empty.

As you can see, the switch operation is to modify the metadata of the table and replace the original allocation unit with the new allocation unit. If the original allocation unit is not empty, then this replacement will cause some loss of data, thus destroying the integrity of the data, so it is not allowed.