How to Move Over 10 lakh Rows in a MiliSecond in Sql Server

In this Article I am explain how to copy the contents of an entire table into another table?  Traditionally speaking, we as developers will use SELECT INTO or a INSERT INTO statement to load a destination table.  This is a still a great way of accomplishing the task at hand, but it is not nearly fast as what I am about to show you.  The method I am about to show you is not for all scenarios, but it can be very handy.

USE [Gangwar]
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'TestData')
BEGIN
    DROP TABLE [dbo].[TestData];
END
GO
CREATE TABLE [dbo].[TestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO

INSERT INTO [dbo].[TestData]
SELECT TOP 10000000 
    ROW_NUMBER() OVER (ORDER BY t1.NAME) AS RowNumber,
    ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, 
    CHAR(ABS(CHECKSUM(NEWID()))%26+65)
    + CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS SomeCode
FROM 
    Master.dbo.SysColumns t1,
    Master.dbo.SysColumns t2
GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'NewTestData')
BEGIN
    DROP TABLE [dbo].[NewTestData];
END
GO

--Create New Table To Move Data To
CREATE TABLE [dbo].[NewTestData](
RowNum INT PRIMARY KEY,
SomeId INT,
SomeCode CHAR(2)
);
GO

--Now the fun part……. behold the power of SQL Server!!!!!!!!!!!!!!!!

--Move data to the new table
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

ALTER TABLE [dbo].[TestData] SWITCH to [dbo].[NewTestData];

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

/*
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
*/

--Next, I will verify the results.

SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000

/*
-----------
0
(1 row(s) affected)
-----------
10000000
(1 row(s) affected)
*/