sequential incremental number - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

sequential incremental number

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2012-08-17 : 12:38:21

Hi I'm trying to update a table of 25,000 rows with a sequential unique number beginning with "P" for product
e.g.
P000001
P000002
P000003
etc

I can start from 1 and just keep going up.

I think I need to declare an Int variable but I am not sure howto do that.

If anyone can help it would really help me out
thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 12:43:32

see

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-17 : 12:49:43

something like this:

update t1
set YourNewColumn = 'P' + right('000000' + convert(varchar(10),rnum),6)
from YourTable t1
join
(select row_number() over (order by (select 1)) as rnum,* from YourTable) dt
on t1.IdColumn = dt.IdColumn

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2012-08-18 : 00:39:39

Thanks - both replys were helpful.
I've done it!!

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-08-25 : 21:42:47

You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...

WITH cte AS
(
 SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
        Product
   FROM #SomeTable
)
 UPDATE cte
    SET Product = CalculatedProductNumber

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 09:28:27

quote:
Originally posted by Jeff Moden

You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...

WITH cte AS
(
SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
Product
FROM #SomeTable
)
UPDATE cte
SET Product = CalculatedProductNumber

--Jeff Moden

Yes. It is also possible to delete through a CTE

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-03 : 09:33:20

quote:
Originally posted by icw

Hi I'm trying to update a table of 25,000 rows with a sequential unique number beginning with "P" for product
e.g.
P000001
P000002
P000003
etc

I can start from 1 and just keep going up.

I think I need to declare an Int variable but I am not sure howto do that.

If anyone can help it would really help me out
thanks in advance

Also read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-09-03 : 10:13:08

quote:
Originally posted by madhivanan

quote:
Originally posted by Jeff Moden

You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...

WITH cte AS
(
SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
Product
FROM #SomeTable
)
UPDATE cte
SET Product = CalculatedProductNumber

--Jeff Moden

Yes. It is also possible to delete through a CTE

Madhivanan

Failing to plan is Planning to fail

And merge. Especially merge....

Practically the entirety of uses I have for CTE are due to merges.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources