Auto ID next value - 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)

Auto ID next value

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-05-22 : 11:31:48

I have a table where ID is not set to autoincrement. When inserting a new record to this table how can I add the next value of ID to the new record.
ID language
1 Afghani
2 Affrikan

when adding next record 'Arabic' it should take 3 as ID?

Nazim
A custom title

1408 Posts

Posted - 2002-05-22 : 11:34:22

declare @a int
select @a=max(id)+1 from table
insert into table values(@a,'Arabic')

or

insert into table select max(id)+1,'Arabic' from table

HTH

--------------------------------------------------------------

Edited by - Nazim on 05/22/2002 11:36:33

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-22 : 11:47:29

If you have a lot of inserts happening at the same time, this way could cause you problems, but you would need a TON of inserts at the same time to run into issues.

Just keep an eye on it.

Michael

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-22 : 11:56:38

You would need to wrap the select and the insert into a transaction using the appropriate isolation level to guarantee success.

Of course, this is really taking the long way 'round ... use an identity column.

quote:

If you have a lot of inserts happening at the same time, this way could cause you problems, but you would need a TON of inserts at the same time to run into issues.
Just keep an eye on it.

Michael

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources