Increment without auto_increment

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

Increment without auto_increment

Author

Topic

stefanth
Starting Member

2 Posts

Posted - 2010-12-13 : 09:55:23

Dear Sir or Madame

I have a table, called OOF in which there is a column called fnr.
The column fnr is of integer type and each entry should be unique.

I want to archive something like this

INSERT INTO OOF (fnr, name) VALUES ((SELECT MAX(fnr) FROM OOF) + 1, 'Hello world');

That is, for each insert the fnr column should be automatically
updated with a unique number.

Can this be done in one line or do I have to split into several lines like

set fnr_new = SELECT MAX(fnr) FROM OOF
set fnr_new = new + 1;
INSERT INTO OOF (fnr, name) VALUES (fnr_new, 'Hello world');

Regards Stefan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-13 : 10:23:45

One way:
INSERT INTO OOF (fnr, name)
SELECT isnull(MAX(fnr),0) + 1, 'Hello world'
FROM OOF

Another:
INSERT INTO OOF (fnr, name)
Select (SELECT isnull(MAX(fnr),0) + 1 FROM OOF), 'Hello world'

Any specific reason for not using identity column

stefanth
Starting Member

2 Posts

Posted - 2010-12-13 : 10:55:42

Thanks a lot for your answear.

It is a commercial bookkeeping database and I can not make any changes
to the structure of the database.

Regards Stefan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-13 : 22:34:11

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources