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.
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 commitGood luck!