sql server 分区表数据迁移中switch的应用

-- create partition function CREATE PARTITION FUNCTION partRange1 (INT) AS RANGE LEFT FOR VALUES (10, 20, 30) ; GO -- create partition scheme CREATE PARTITION SCHEME partScheme1 AS PARTITION partRange1 ALL TO ([PRIMARY]) ; GO -- create table that uses this partitioning scheme CREATE TABLE partTable (col1 INT, col2 VARCHAR(20)) ON partScheme1 (col1) ; GO --查询分区情况 SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('partTable') GO -- insert some sample data INSERT INTO partTable (col1, col2) VALUES (5, 'partTable') INSERT INTO partTable (col1, col2) VALUES (6, 'partTable') INSERT INTO partTable (col1, col2) VALUES (7, 'partTable') GO -- select the data SELECT * FROM partTable go -- switch in CREATE TABLE newPartTable (col1 INT CHECK (col1 > 30 AND col1 <= 40 AND col1 IS NOT NULL), col2 VARCHAR(20)) GO -- insert some sample data into new table INSERT INTO newPartTable (col1, col2) VALUES (31, 'newPartTable') INSERT INTO newPartTable (col1, col2) VALUES (32, 'newPartTable') INSERT INTO newPartTable (col1, col2) VALUES (33, 'newPartTable') -- select the data SELECT * FROM partTable SELECT * FROM newPartTable go -- make the switch ALTER TABLE newPartTable SWITCH TO partTable PARTITION 4; GO -- select the data SELECT * FROM partTable SELECT * FROM newPartTable go -- switch out CREATE TABLE nonPartTable (col1 INT, col2 VARCHAR(20)) ON [primary] ; GO -- make the switch ALTER TABLE partTable SWITCH PARTITION 1 TO nonPartTable ; GO -- select the data SELECT * FROM partTable SELECT * FROM nonPartTable