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
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.
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment