Question :
I’m new to partition switching and have used this basic syntax previously:
TRUNCATE TABLE TARGET
ALTER TABLE SOURCE SWITCH TO TARGET
I just came across an SSIS package where there is this syntax within a loop:
TRUNCATE TABLE TARGET
ALTER TABLE SOURCE
SWITCH PARTITION $PARTITION.MONTHFN(@MONTH) TO TARGET PARTITION $PARTITION.MONTHFN(@MONTH)
If all the partitions are being switched to the target table, is it necessary to do the switching by partition? Is this only because the source is already partitioned?
Answer :
This syntax for ALTER TABLE SWITCH requires you to specify a single partition.
ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name
{
ALTER COLUMN column_name
{
type_name [ ( precision [ , scale ] ) ]
[ COLLATE Windows_collation_name ]
[ NULL | NOT NULL ]
}
| ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
| REBUILD {
[ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
| [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
}
| { SPLIT | MERGE } RANGE (boundary_value)
| SWITCH [ PARTITION source_partition_number
TO target_table_name [ PARTITION target_partition_number ]
}
[;]
I found several articles that show different ways to automate the switching of all partitions – this is just one example
DECLARE @Partitions TABLE (PartitionId int PRIMARY KEY CLUSTERED);
DECLARE @PartitionId INT;
INSERT @Partitions(PartitionId)
SELECT
prv.boundary_id PartitionId
FROM sys.partition_functions AS pf
INNER JOIN sys.partition_range_values prv ON prv.function_id=pf.function_id
WHERE (pf.name=N'PartitionFunctionName');
WHILE EXISTS (SELECT NULL FROM @Partitions)
BEGIN
SELECT TOP 1 @PartitionId = PartitionId FROM @Partitions;
ALTER TABLE MS_PROD SWITCH PARTITION @PartitionId TO MS_Stage PARTITION @PartitionId;
RAISERROR('Switched PartitionId %d to Stage',0,1,@PartitionId) WITH NOWAIT;
DELETE @Partitions WHERE PartitionId = @PartitionId;
END