Create a table that is defined the same as the target table;
Add constraints to the table to ensure that the data of the table is in the target partition;
Alter table source table switch to target table partition partition number
The partition number can be obtained through $partition.partion_func( column value)
This process is much faster than insert select, because it does not involve io, you only need to modify the metadata, the onwer of the edge partition.
However, the above process is only applicable to the situation where the target partition already exists in the partition function of the target table. For example, if you define partition functions with boundaries of 1, 2, and 3, then 4 and 5 will be inserted into the 4th partition.
So I recommend using this method of defining all partitions of partition functions in advance. If you want to partition by day, you should first define 1,000 partitions. This method is relatively simple and does not require checking when adding data, resulting in errors.
If you must use dynamic partitions, when adding a source table as a new partition, you need to do the following:
First get the value of the newly added partition column and set it to X;
Find all boundary values of the target partition function through sys.Partition_range_values and sys.partition_functions;
Perform the following check:
Here, there is a difference between the division value belonging to the left partition or the right partition, and we assume that it belongs to the left partition;
If X exists in the 2-step boundary value set, then congratulations, no additional operations are required;
If X is greater than the largest boundary in 2 steps, you not only need to split a partition for X, but also ensure that data smaller than X has its own partition.
Check the existing boundary value every time you join a partition. If it is not equal to any of them, split it with X as the parameter, and delete the same data as X in the target table.
Switch the source table into the target partition as described previously.
If data already exists in the target partition at 2 steps, the alter ...switch statement will fail because the target partition is not empty.
It can be understood that 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 data loss, thereby destroying the integrity of the data, so it is not allowed.
Add constraints to the table to ensure that the data of the table is in the target partition;
Alter table source table switch to target table partition partition number
The partition number can be obtained through $partition.partion_func( column value)
This process is much faster than insert select, because it does not involve io, you only need to modify the metadata, the onwer of the edge partition.
However, the above process is only applicable to the situation where the target partition already exists in the partition function of the target table. For example, if you define partition functions with boundaries of 1, 2, and 3, then 4 and 5 will be inserted into the 4th partition.
So I recommend using this method of defining all partitions of partition functions in advance. If you want to partition by day, you should first define 1,000 partitions. This method is relatively simple and does not require checking when adding data, resulting in errors.
If you must use dynamic partitions, when adding a source table as a new partition, you need to do the following:
First get the value of the newly added partition column and set it to X;
Find all boundary values of the target partition function through sys.Partition_range_values and sys.partition_functions;
Perform the following check:
Here, there is a difference between the division value belonging to the left partition or the right partition, and we assume that it belongs to the left partition;
If X exists in the 2-step boundary value set, then congratulations, no additional operations are required;
If X is greater than the largest boundary in 2 steps, you not only need to split a partition for X, but also ensure that data smaller than X has its own partition.
Check the existing boundary value every time you join a partition. If it is not equal to any of them, split it with X as the parameter, and delete the same data as X in the target table.
Switch the source table into the target partition as described previously.
If data already exists in the target partition at 2 steps, the alter ...switch statement will fail because the target partition is not empty.
It can be understood that 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 data loss, thereby destroying the integrity of the data, so it is not allowed.