SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 select max then add 1 and insert...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sirtcarlos
Starting Member

10 Posts

Posted - 06/25/2008 :  16:08:11  Show Profile  Reply with Quote
i need to basically do multiple queries and functions at the same time here and am sorta lost at how to do it.
what i'm trying to do is
select max(id_number)
from table

then

take the max(id_number), add '1' and insert a new value into the table with the new id_number.
i hope that makes sense...i've been going in circles as to how to do this all at once, so any help is much appreciated.
thanks

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/25/2008 :  16:10:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why don't you just use the identity option for the column instead that way you don't have to manage this, but rather SQL Server will?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 06/25/2008 :  16:12:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How come teacher don't teach the concept of IDENTITY anymore?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/25/2008 :  16:18:56  Show Profile  Reply with Quote
no, actually it isnt that simple...i dont think...
basically what's happening is a user clicks 'Add New' and when they click that button, they are taken to a new page on which there is a form to add a new record to the db. currently it is putting a new id_number in through getting the max, setting it as a variable (in CF) and adding 1, and THEN when the user submits the form it writes everything into the db.
however...
if while that user is entering the info onto the form, another user clicks 'Add New' they will get an identical id_number and all id_numbers have to be unique (and preferably incremental).
so what we need is for an id_number to be written as soon as they enter the page, whether or not they complete the form, just to ensure that all id_numbers are different.
i hope that makes a little more sense.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 06/25/2008 :  16:21:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes.
And your problem is solved by using an IDENTITY.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/25/2008 :  16:23:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here's a quick example:

CREATE TABLE #Temp (Column1 int IDENTITY(1, 1), Column2 varchar(10))

INSERT INTO #Temp VALUES('Tara')
INSERT INTO #Temp VALUES('Mike')

SELECT * FROM #Temp

DROP TABLE #Temp

Notice how I didn't provide a value for Column1, yet I have data in it that is autonumbered. That's what an identity column does for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 06/25/2008 16:23:59
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/25/2008 :  16:50:09  Show Profile  Reply with Quote
Tara,
first, thanks for the quick responses without the condescending comments about what i haven't been taught...
next, i just want to be sure i fully understand what is going on.
ok...so i get what you're saying and was all set to modify my table to set that column type to IDENTITY and yes that would have probably solved my problems...but that would be too easy.
there is already a column set to IDENTITY. not sure why there are two columns like this (someone else built the table), but i can't change that, and i believe i can only have one column set to IDENTITY. so do you have another option?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/25/2008 :  17:01:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
You are correct that you can't have multiple columns with the identity option.

I would highly recommend that you modify this table so that you use an identity column. All other autonumber solutions have their issues such as concurrency of multiple inserts. The concurrency problem would lead to performance issues as you'd have to lock the table when grabbing the max+1 value and then writing the row. Plus you wouldn't be able to easily insert multiple rows in one query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/25/2008 :  17:05:15  Show Profile  Reply with Quote
well the problem is we dont want the unique identifier id to be visible and the same as the id_number that the user sees and is assigned to each form item. so we need two separate columns, and the table is already populated so the numbers dont match already. for example, we have 500 rows, but currently the id_number is 900.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 06/25/2008 :  17:07:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
When you say unique identifier id, are you referring to a GUID (uniqueidentifier) column or an identity column?

It doesn't matter that you have 500 rows and the current id_number is 900. Identity can handle that just fine.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/25/2008 :  17:16:02  Show Profile  Reply with Quote
the current table setup is:
ID - int, primary key, identity - hidden id col for each record
ID_NUMBER - int - visible id col that needs to become incremental
[then other various cols]

i realize that identity will just increment from the previous value, i just cant take the identity off of the 'ID' column, thus i feel like i need a method to incrementally add to the ID_NUMBER col.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 06/25/2008 :  17:19:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Do you mean INCREMENTAL or SEQUENTIAL?

You can make the ID_NUMBER columns as a calculated column over ID.
See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/26/2008 :  08:42:28  Show Profile  Reply with Quote
sequential is fine...i don't really understand the difference...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 06/26/2008 :  09:37:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sirtcarlos

sequential is fine...i don't really understand the difference...


What do you do with ID_NUMBER?
Does a gap matter?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/26/2008 :  10:01:54  Show Profile  Reply with Quote
the ID_NUMBER is just a reference for us to use for project identifiers. gaps are fine, we just need to ensure that the same number cant be set on multiple projects.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 06/26/2008 :  10:08:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sirtcarlos

the ID_NUMBER is just a reference for us to use for project identifiers. gaps are fine, we just need to ensure that the same number cant be set on multiple projects.


If you dont worry about the gaps, Indentity column is exactly what you need. All you have to do is to exclude it in the Insert statement so that it would be created automatically

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/26/2008 :  10:19:02  Show Profile  Reply with Quote
madhivanan,
thanks for the help, but as i indicated in an earlier thread, i already have another column as the IDENTITY, and cannot change that, and since i cannot have 2 IDENTITY cols, i need an alternative.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 06/26/2008 :  10:30:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by sirtcarlos

madhivanan,
thanks for the help, but as i indicated in an earlier thread, i already have another column as the IDENTITY, and cannot change that, and since i cannot have 2 IDENTITY cols, i need an alternative.


Why dont you make use of the existing identity column and forget about other column?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sirtcarlos
Starting Member

10 Posts

Posted - 06/26/2008 :  10:33:28  Show Profile  Reply with Quote
not an option, i'm told we dont want to have the ID displayed to users and that technically this number could be changed if we want to. we need the normal ID column that is the IDENTITY col, AND we need this second column...plus the table is already populated with a lot of projects and has been in use, so we cant make huge changes that may effect everything else.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 06/26/2008 :  11:04:26  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
The link Peso posted seems to be exactly what you need.
http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

It works basically by taking the [Id] column that's autogenerated when you insert and adding <something> to it. You will have to make a function to do this but that is covered in the link.

If you need new inserts going forward in your table to be greater than previous ones you already have, just choose a value currently higher than any id_number you currently have and then add the Id to it. So your function would effectivly add the current Id (auto generated) to a number (say 50000) guaranteed to be higher (set by you) to any that was generated by the old method - to generate a new Id_number. It's guaranteed to be unique because it's based of your IDENTITY column.

-------------
Charlie
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000