Labels

Wednesday, May 4, 2011

How to Use Switch To ?

USE [tempdb]
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)
);

--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

SELECT COUNT(*) FROM [dbo].[TestData]; --0
SELECT COUNT(*) FROM [dbo].[NewTestData]; --10,000,000
Select * into #temp from [NewTestData]

No comments:

Post a Comment