MSSQL table switch IN, switch OUT examples…
-- Switch in/out procedure examples...
-- Tim van Kooten Niekerk
-- Partition function en schema maken...
--DROP PARTITION FUNCTION pfTable_1
--DROP PARTITION SCHEME psTable_1
CREATE PARTITION FUNCTION pfTable_1 (int) AS RANGE LEFT FOR VALUES (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020)
CREATE PARTITION SCHEME psTable_1 AS PARTITION pfTable_1 ALL TO ([PRIMARY])
-- Make table with a partition funciton on column jaar (year)...
CREATE TABLE [dbo].[Table_3](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] NOT NULL
) ON [psTable_1]([jaar])
-- Check partitioned table...
SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Table_3') ORDER BY partition_number
-- Make Switch OUT and truncate table so you can reload 2015...
-- Make Switch OUT table without constraints...
CREATE TABLE [dbo].[Table_3_OUT](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] NOT NULL
)
ALTER TABLE Table_3 SWITCH PARTITION 5 TO Table_3_OUT
TRUNCATE TABLE [dbo].[Table_3_OUT]
-- Now you can reload (INSERT) new values for 2015 in the original table...
-- Or... Make Switch IN with using a switch IN table for 2015...
-- Switch IN table with constraint on jaar = 2015...
CREATE TABLE [dbo].[Table_3_IN](
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [nchar](10) NULL,
[jaar] [int] CHECK (jaar = 2015) NOT NULL
)
-- INSERT new values in switch IN table and make switch...
ALTER TABLE Table_3_IN SWITCH TO Table_3 PARTITION 5
-- Extend partition function with a new year...
ALTER PARTITION SCHEME psTable_1 NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION pfTable_1 () SPLIT RANGE (2021)