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
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
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 ;
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)
OP need repetitive
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
