| Author |
Topic |
|
sirtcarlos
Starting Member
10 Posts |
 Posted - 2008-06-25 : 16:08:11
|
| 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 isselect max(id_number)from tablethentake 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
38200 Posts |
Posted - 2008-06-25 : 16:10:17
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-25 : 16:12:59
|
How come teacher don't teach the concept of IDENTITY anymore?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-25 : 16:18:56
|
| 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-25 : 16:21:35
|
Yes.And your problem is solved by using an IDENTITY.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-25 : 16:23:31
|
| 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 #TempDROP TABLE #TempNotice 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-25 : 16:50:09
|
| 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? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-25 : 17:01:50
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-25 : 17:05:15
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-25 : 17:07:42
|
| 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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-25 : 17:16:02
|
| the current table setup is:ID - int, primary key, identity - hidden id col for each recordID_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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-26 : 08:42:28
|
| sequential is fine...i don't really understand the difference... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-26 : 09:37:19
|
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?MadhivananFailing to plan is Planning to fail |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-26 : 10:01:54
|
| 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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-26 : 10:08:41
|
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 automaticallyMadhivananFailing to plan is Planning to fail |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-26 : 10:19:02
|
| 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. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-26 : 10:30:53
|
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?MadhivananFailing to plan is Planning to fail |
|
|
|
sirtcarlos
Starting Member
10 Posts |
Posted - 2008-06-26 : 10:33:28
|
| 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. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-06-26 : 11:04:26
|
| The link Peso posted seems to be exactly what you need.http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-serverIt 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 |
|
|
|
|
select max then add 1 and insert...