Migrating data in SQL Server instantly with SWITCH

*Note: This only pertains to the ENTERPRISE edition of SQL Server 2005 +

I was working the other day with a very large table (10 billion + records). I was tasked with migrating this table to a new paritioned environment. When all was said and done the new table had 409 partitions (that's a story for another day).

In any case, when all of the data was migrated over (took 5 days as some transformation had to be done). I realized I was a total noob and forgot to set the identity column in the new table (du'oh!).

So what to do?

Creating a new column via ALTER TABLE would have placed locks on the database that would have locked out various schema bound functions other developers were running for at least a day... not the best option. I like my co-workers.

So I came across SWITCH.

In the most basic sense, SWITCH can be used to swap data between two tables:

 CREATE TABLE TestTable  (  id int,

column1 varchar(100),

column2 varchar(100)  );

 CREATE TABLE TestTable2  (  id int identity(1,1),

column1 varchar(100),

column2 varchar(100)  );

 ALTER TABLE TestTable SWITCH TO TestTable2;

But remember that my table has 409 partitions? Yeah this would give the awesome error:

Msg 4911, Level 16, State 1, Line 2 Cannot specify a partitioned table without partition number in ALTER TABLE SWITCH statement. The table 'TestTable' is partitioned.

To work around this we would need to loop through the partitions. In the end with the below code, I migrated 10 billion+ rows across 409 partitions, in under 30 seconds. Good times.

Also excuse the poor code formatting... I'm new to this blog thing.

DECLARE @i INT = 1  
DECLARE @sql varchar(max) 
WHILE @i < 410 
 BEGIN  
  SET @sql = 'ALTER TABLE PartitionedTable1 SWITCH partition '+CAST(@i as varchar(5)) + 'TO PartitionedTable2 partition '+CAST(@i as varchar(5))  
  EXEC(@sql)  
  SELECT @i = @i + 1 
 END

Cheers!