incrementing a 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 2000 Forums

SQL Server Development (2000)

incrementing a number

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-01-13 : 08:51:30

MICHAEL writes "I have a table called REQUEST NUMBER, it has one field (req_num) which is numeric. I need a statement that will get me the number that is there then increment it by one and reinsert that number. Thanks

Michael"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 08:59:33

Declare @id int
Select Isnull(Max(id),0)+1 from yourTable
Insert into yourTable (columns) values (@id,....)

Madhivanan

Failing to plan is Planning to fail

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-13 : 09:01:33

DECLARE @newid numeric
SELECT @newid=MAX(req_num) FROM [REQUEST NUMBER] will give you maximum value and you can increment it by 1
SET @newid = @newid +1

and then you can use your insert statement

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-13 : 09:06:09

Give examples of
Existing data
New or changed data

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-13 : 09:13:55

quote:
Originally posted by cshah1

DECLARE @newid numeric
SELECT @newid=MAX(req_num) FROM [REQUEST NUMBER] will give you maximum value and you can increment it by 1
SET @newid = @newid +1

and then you can use your insert statement

@newid will be null if there are no rows in the table

Madhivanan

Failing to plan is Planning to fail

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2006-01-13 : 09:25:09

You are correct and I realized seconds after your post..Namaste!

mr_mist
Grunnio

1870 Posts

Posted - 2006-01-13 : 09:34:08

Of course all of the above could introduce errors with consistency, or locking if you transactionalise them. You're really re-inventing the IDENTITY field wheel.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources