T-SQL: How to switch all partitions from one table to another?

Question

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

Single statement? I don't think so. You have to beg to proc. — stewen.net, May 15 '12 at 10:37
this post from stackoverflow worked for me:
http://stackoverflow.com/questions/17606379/sql-server-switch-more-than-one-partition-at-once — , Aug 17 '15 at 19:35

score 6 · Answer 1 · edited Feb 02 '17 at 19:47

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

score 2 · Answer 2 · answered Oct 17 '11 at 19:16

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.

T-SQL: How to switch all partitions from one table to another?

2 Answers2

Linked