Loop Thru Record set and assign number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loop Thru Record set and assign number

Author

Topic

denni50
Starting Member

8 Posts

Posted - 2013-11-07 : 10:05:03

Hello Everyone - I am new to SQL Server (coming from Oracle background) and have a large table I need to loop thru and assign a number 1 thru 5 on each record.

below is an example of how I would do it in Oracle.

declare
num number := 1;
cursor c_rec is SELECT rowid, t.* FROM temp t order by t.column;
begin
for d_rec in c_rec
loop
update temp set column = to_char(num)
where rowid = d_rec.rowid;
num:= num + 1;
if num>5 then
num:=1;
end if;
end loop;
commit;
end;

can someone show me the equivalent for SQL Server.

thanks for helping.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-07 : 10:17:29

How would you explain your problem to someone who isn't knowing anything about oracle?
Maybe you would give table structure, sample data and wanted result?

Too old to Rock'n'Roll too young to die.

denni50
Starting Member

8 Posts

Posted - 2013-11-07 : 10:20:16

quote:
Originally posted by webfred

How would you explain your problem to someone who isn't knowing anything about oracle?
Maybe you would give table structure, sample data and wanted result?

Too old to Rock'n'Roll too young to die.

denni50
Starting Member

8 Posts

Posted - 2013-11-07 : 10:26:26

Hi,

I don't have access to the SQL Server db all the time to provide test data as I am in training right now, the concept is you have table with 100 records like below and you want to filter through all the records and assign them a number 1 thru 5 like below

Id num
100 1
120 2
130 3
140 4
150 5
160 1
170 2
180 3
190 4
200 5
210 1
.....etc

thanks/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-07 : 10:40:57

select
ntile(5) over (order by Id) as rnum, * from YourTable

Too old to Rock'n'Roll too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-07 : 10:54:44

I do not think NTILE will put num in the right order.

Try:


-- *** Test Data ***
CREATE TABLE #t
(
	ID int NOT NULL
	,num int NULL
)
INSERT INTO #t(ID)
VALUES (100),(120),(130),(140),(150),(160),(170),(180),(190),(200),(210)
-- *** End Test Data ***

select * from #t;

WITH NewNums
AS
(
	SELECT *
		,( (ROW_NUMBER() OVER (ORDER BY ID) - 1) % 5) + 1 AS NewNum
	FROM #t
)
UPDATE NewNums
SET num = NewNum;

select * from #t;

denni50
Starting Member

8 Posts

Posted - 2013-11-07 : 11:20:28

thanks IFOR,

your solution worked like a charm looping thru 3 million records in 3 minutes and assigning the number in blocks of 5.

can you recommend a good Sql Server programming for newbies book.

I'm finding code is much different from MSAccess VBA and Oracle.

thanks again!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-07 : 12:17:22

The Microsoft books by Itzik Ben Gan are good for t-sql.

The main trick with t-sql is to avoid cursors like the plague.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources