Monday, January 5, 2009

Creating refCount for new Databases

Found I was tired copying table structure and then content for refCount to new databases. Below is a simple piece of SQL to create a large refCount quickly and easily.

USE [dbname]
GO

/****** Object: Table [dbo].[refCount] Script Date: 01/05/2009 12:25:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[refCount](
[Count] [int] NOT NULL,
CONSTRAINT [PK_refCount] PRIMARY KEY CLUSTERED
(
[Count] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--0 entered
INSERT INTO refCount ([Count])
SELECT 0

--1 entered
INSERT INTO refCount ([Count])
SELECT 1

DECLARE @MaxCount int
SELECT @MaxCount = MAX([Count]) FROM refCount
--3 entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

DECLARE @Y int
--IMPORTANT!!
--Change this to suit your requirements where MaxCount will be [2 to the power of (Y + 2)]
SET @Y = 10 --MaxCount of your refCount after running will be 4096 ie [2 to the power of (10 + 2)]

DECLARE @Count int
DECLARE c1 CURSOR FOR SELECT * FROM refCount WHERE [Count] < @Y
OPEN c1
FETCH NEXT FROM c1 INTO @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @MaxCount = MAX([Count]) FROM refCount
--entered
INSERT INTO refCount ([Count])
SELECT ([Count] + @MaxCount + 1) FROM refCount

FETCH NEXT FROM c1 INTO @Count
END

CLOSE c1
DEALLOCATE c1 --End Vendor Due

No comments: