[SQL]无分区表到分区表的数据SWITCH

关键的一点：无分区表一定要添加对应分区列的CONSTRAINT

创建两个表，一个将分区，另一个没有分区

CREATE TABLE PARTITION_TABLE

(

ID int NULL --注意

)

CREATE TABLE NON_PARTITION_TABLE

(

ID int NULL --注意

)

在 PARTITION_TABLE 创建分区, 使用Left boundary,初始创建两个分区,<=20090101, <20090101

在NON_PARTITION_TABLE中插入数据

INSERT NON_PARTITION_TABLE

VALUES(20090101)----第一个分区

SWITCH 数据，从 NON_PARTITION_TABLE 到 PARTITION_TABLE

ALTER TABLE NON_PARTITION_TABLE SWITCH TO PARTITION_TABLE PARTITION $PARTITION.f$partition(20090101)

执行失败：

Msg 4982, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. Check constraints of source table 'EDI.dbo.NON_PARTITION_TABLE' allow values that are not allowed by range defined by partition 1 on target table 'EDI.dbo.PARTITION_TABLE'.

解决方法：需要添加CONSTRAINT在NON_PARTITION_TABLE

ALTER TABLE NON_PARTITION_TABLE WITH CHECK ADD CONSTRAINT CC_partition CHECK(ID = 20090101)

再次执行

ALTER TABLE NON_PARTITION_TABLE SWITCH TO PARTITION_TABLE PARTITION $PARTITION.f$partition(20090101)

执行成功

关键在于，此时添加的CONSTRAINT 还是不够的

看下面情况

删除NON_PARTITION_TABLE 的数据和CONSTRAINT

再次插入数据

INSERT NON_PARTITION_TABLE

VALUES(20110101)--最后一个分区

添加CONSTRAINT在NON_PARTITION_TABLE

ALTER TABLE NON_PARTITION_TABLE WITH CHECK ADD CONSTRAINT CC_partition CHECK(ID = 20110101)

再次SWITCH数据，从 NON_PARTITION_TABLE 到 PARTITION_TABLE

ALTER TABLE NON_PARTITION_TABLE SWITCH TO PARTITION_TABLE PARTITION $PARTITION.f$partition(20110101)

却发现执行失败，

Msg 4972, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'EDI.dbo.NON_PARTITION_TABLE' allows values that are not allowed by check constraints or partition function on target table 'EDI.dbo.PARTITION_TABLE'.

跟上次解决方法一样，怎么会失败呢？

是因为CONSTRAINT 还缺少个条件: ID IS NOT NULL.

去掉原来的CONSTRAINT,添加新的

ALTER TABLE NON_PARTITION_TABLE WITH CHECK ADD CONSTRAINT CC_partition CHECK(ID = 20110101 AND ID IS NOT NULL)

再次执行SWITCH 转换将会成功

注意： 如果在创建表的初始时ID 为NOT NULL 则第二种情况则不会出现