CODEWITHSUNDEEP
sql-serversql-server-2008
DoubleD
DoubleD

Reputation: 91

Generate a repetitive sequential number using SQL Server 2008

Can anyone help me to generate a repetitive sequential number using SQL Server 2008. Say I have a table of 1000 rows and a new field (int) added to the table. All I need is to auto fill that particular field with sequential numbers 1-100 all the way to the last row.

I have this but doesnt seem that it is working. You help is much appreciated.

DECLARE @id INT 
SET @id = 0 
while @id < 101
BEGIN
    UPDATE Names SET id=@id
    set @id=@id+1
END

Upvotes: 1

Views: 3862

Answers (4)

chris
chris

Reputation: 13

Your way isn't working because you are trying to set a value rather than insert one. I'm sure you have found a solution by now but if not then try this instead.

DECLARE @id INT 
SET @id = 0 
while @id < 101
BEGIN
    INSERT Names select @id
    set @id=@id+1
END

Upvotes: 0

SAS
SAS

Reputation: 4035

Not pretty or elegant, but..

while exists(select * from tbl where new_col is null) 
update top(1) tbl 
set new_col=(select ISNULL(max(new_col),0)+1 from tbl WHERE new_col is null)

Upvotes: 0

Greenstone Walker
Greenstone Walker

Reputation: 1150

Use ROW_NUMBER to generate a number. Use modulo maths to get values from 1 to 100.

go
create table dbo.Demo1
(
    DID int not null identity primary key,
    RepetitiveSequentialNumber int not null 
) ;
go
insert into dbo.Demo1 values ( 0 )
go 1000 -- This is just to get 1,000 rows into the table.

-- Get a sequential number.
select DID, row_number() over ( order by DID ) as RSN
into #RowNumbers
from dbo.Demo1 ;
-- Take the last two digits. This gives us values from 0 to 99.
update #RowNumbers
set RSN = RSN % 100 ;
-- Change the 0 values to 100.
update #RowNumbers 
set RSN = case when RSN = 0 then 100 else RSN end ;
-- Update the main table.
update dbo.Demo1 
set RepetitiveSequentialNumber = r.RSN 
from dbo.Demo1 as d inner join #RowNumbers as r on r.DID = d.DID ;

select * 
from dbo.Demo1 ;

Upvotes: 0

rudi bruchez
rudi bruchez

Reputation: 642

USE tempdb
GO

DROP TABLE tableof1000rows
GO
CREATE TABLE tableof1000rows (id int identity(1,1), nb int, value varchar(128))
GO

INSERT INTO tableof1000rows (value)
SELECT TOP 1000 o1.name
FROM sys.objects o1
CROSS JOIN sys.objects o2
GO

UPDATE t1
SET nb = t2.nb
FROM tableof1000rows t1
JOIN (SELECT id, (ROW_NUMBER() OVER (ORDER BY id) % 100) + 1 as nb FROM tableof1000rows) t2 ON t1.id = t2.id
GO

SELECT *
FROM tableof1000rows

Upvotes: 1

Related Questions