Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 17 '11 at 16:06

This question came from our site for professional and enthusiast programmers.

    
Single statement? I don't think so. You have to beg to proc. –  stewen.net May 15 '12 at 10:37

2 Answers 2

For anyone wanting a solution to this, here's a sproc 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
share|improve this answer
    
You should use nvarchar for object names and quotename when concatenating them into strings to avoid it failing with non standard object identifiers. –  Martin Smith Nov 14 '11 at 11:01

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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