0 Comments

I have a table filled with numbers. It is a table with the name numbers and there is only one field named ‘num’ which is an integer (so max value is 2.147.483.647 as seen on MSDN) and the integer is  an identity and unique.

2014-03-05 12_10_47-VM530.ssinternational - dbo.numbers - Microsoft SQL Server Management Studio

The table just contains numbers from 1 until 10.000. I use it to inner join, for instance:

You can have a table with a row: productA is 10 times in stock. You can then inner join the numbers table if you need 10 rows of productA.

select * from products 
inner join numbers on num <= stock

But I only had 10.000 rows in it and encountered a bug in my system because 10.000 was not enough. So I had to increase it. I have found several solutions to create a numbers table. But no snippet to update an existing number table.

So here is my snippet to add additional numbers to your number table:

begin transaction
SET IDENTITY_INSERT [numbers] ON

DECLARE @i INT;
SELECT @i = 10001;
SET NOCOUNT ON
WHILE @i <= 1000000
BEGIN
    INSERT INTO [numbers] (num) VALUES (@i);
    select @i= @i + 1;
end;

SET IDENTITY_INSERT [numbers] OFF
commit
Good luck!
Pin on pinterest Plus on Googleplus Post on LinkedIn