[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1092
  • Last Modified:

Adding a sequence (range) of numbers to a existing table

Hi there,

I need to add a sequence of numbers using SQL into a existing table. I need help with making the sql query (not a stored procedure) which can do this.

I have a range of numbers (basically i am adding on to an existing range of numbers in the table) and I need to add the range say between 1000 and 2000 (in to column rangedvalue) in a table with the following columns:

primkey (pk)    |      rangedvalue   |   always0 |    isAlwaysNull
1                              1000                     0               NULL
2                              1001                     0               NULL
3                              1002                     0               NULL
4                              1003                     0               NULL  
.
.
n                              nseq                     0               NULL

You can use my column names for the query.

Many thanks in advance for your support.                  
0
ihatelag
Asked:
ihatelag
1 Solution
 
i2mentalCommented:
declare @count int
declare @pkcount int

set @pkcount = 1
set @count = 1000

while @count <= 2000
begin
insert into table (primkey, rangedvalue, always0, isAlwaysNull)
values (@pkcount, @count, 0, null)
set @count = @count + 1
set @pkcount = @pkcount + 1
end
0
 
dportasCommented:
WITH t AS
 (SELECT ROW_NUMBER() OVER (ORDER BY primkey)+999 r,
  rangedvalue
  FROM tbl)
UPDATE t SET rangedvalue = r;
0
 
Scott PletcherSenior DBACommented:
UPDATE table
SET rangevalue = primkey + 999
0
 
ihatelagAuthor Commented:
PERFECT! :D Thanks for this, most appreciated! You just saved my life :P
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now