4

Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?

2
6

For anyone wanting a solution to this, here's a stored procedure I cooked up:

------------------------------------------------------
-- Switches all non-empty partitions from a switch_in
-- table into a target table, and switches all replaced
-- partitions from the target table into a switch_out
-- table.
------------------------------------------------------
CREATE PROC [dbo].[SwitchAllPopulatedPartitions]
        @table     varchar(1000),
        @table_in  varchar(1000) = null,
        @table_out varchar(1000) = null
AS  
    SET @table_in = ISNULL(@table_in, @table + '_SwitchIn');
    SET @table_out = ISNULL(@table_out, @table + '_SwitchOut');

    DECLARE @object_id     int = OBJECT_ID(@table);
    DECLARE @object_id_in  int = OBJECT_ID(@table_in );
    DECLARE @object_id_out int = OBJECT_ID(@table_out );

    DECLARE @SQL varchar(max) = 'TRUNCATE TABLE ' + @table_out + ';' + CHAR(13);

    SELECT @SQL = @SQL
                + 'ALTER TABLE ' + @table + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table_out + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
                + 'ALTER TABLE ' + @table_in + ' SWITCH PARTITION ' + CAST(partition_number as varchar(10))
                + ' TO ' + @table + ' PARTITION ' + CAST(partition_number as varchar(10)) + '; ' + CHAR(13)
    from sys.partitions
    where OBJECT_ID = @object_id_in and index_id = 1 and rows > 0

    SET @SQL = @SQL + 'TRUNCATE TABLE ' + @table_in + ';' + CHAR(13);

    EXEC (@SQL);
GO
0
2

No. I don't believe this is possible. The staging table must have a constraint on it that matches a specific partition and ALTER TABLE SWITCH PARTITION will only work with one partition at a time.

The closest you could do to this is to write something that iterated over the partition ranges and swapped the table to a temp table and then into another partitioned table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.