SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 06:31 AM
| ||||
| ||||
 Identity In SQL VS Autonumber In Access Hi I wonder if anyone can help with the following: when using an autonumber in access when you use .addnew you automatically get the field in an autonumber field i.e. rs.addnew jno=autonofld rs.update jno will have a value. however in SQL you have to update first and then find the record (I may be wrong) is there a way to get the field value before doing the update in SQL Server ? TIA Steve      |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access Normally you use SCOPE_IDENTITY to return the IDENTITY value after the INSERT. Could you explain why you want the IDENTITY value before insertion? How do you intend to use the returned value? There are some strategies you could use, such as generating a value first and then INSERTing it. If you want to use an IDENTITY column though I'm not sure what benefit you would gain by knowing the value beforehand. Serializing INSERTs isn't recommended because that approach doesn't scale well. It shouldn't be necessary with an IDENTITY column anyway. -- David Portas SQL Server MVP -- |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access Thanks for the response I need the Identity value at the time of insertion so I can display a Job Number to the user as soon as the record is added. The reason I used the Access example is to illustrate the retrieving of the value before .update was to show what I wanted to do (sorry it gave the wrong idea). I will try the SCOPE_IDENTITY. Thanks Again Steve "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:1110275881.484473.10290@z14g2000cwz.googlegro ups.com... > Normally you use SCOPE_IDENTITY to return the IDENTITY value after the > INSERT. Could you explain why you want the IDENTITY value before > insertion? How do you intend to use the returned value? There are some > strategies you could use, such as generating a value first and then > INSERTing it. If you want to use an IDENTITY column though I'm not sure > what benefit you would gain by knowing the value beforehand. > > Serializing INSERTs isn't recommended because that approach doesn't > scale well. It shouldn't be necessary with an IDENTITY column anyway. > > -- > David Portas > SQL Server MVP > -- > |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I forgot to mention which version of SQL Server I am using) do you have any other suggestions ? Steve "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:1110275881.484473.10290@z14g2000cwz.googlegro ups.com... > Normally you use SCOPE_IDENTITY to return the IDENTITY value after the > INSERT. Could you explain why you want the IDENTITY value before > insertion? How do you intend to use the returned value? There are some > strategies you could use, such as generating a value first and then > INSERTing it. If you want to use an IDENTITY column though I'm not sure > what benefit you would gain by knowing the value beforehand. > > Serializing INSERTs isn't recommended because that approach doesn't > scale well. It shouldn't be necessary with an IDENTITY column anyway. > > -- > David Portas > SQL Server MVP > -- > |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access Madhivanan (madhivanan2001@gmail.com) writes: > Select Max(IdentityField)+1 from TableName That's not a good thing, since the value you get may not actually be that value, if another process comes in between. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access Steve (stevej@ufrmsa1.uniforum.org.za) writes: > SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I > forgot to mention which version of > SQL Server I am using) do you have any other suggestions ? Use @@identity instead. If you have a trigger on the table that inserts into a secont identity table, @@identity will have the value from that table. This is why scope_identity() is usually recommended, since it returns the most recently used identity value in the current scope. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp |
|
02-29-2008, 06:31 AM
| |||
| |||
| Re: Identity In SQL VS Autonumber In Access Thanks, but I have to agree with Erland Steve "Madhivanan" <madhivanan2001@gmail.com> wrote in message news:1110279816.145987.160610@f14g2000cwb.googlegr oups.com... > > Steve, > > Select Max(IdentityField)+1 from TableName > > Madhivanan > |
|
02-29-2008, 06:31 AM
| ||||
| ||||
| Re: Identity In SQL VS Autonumber In Access Thanks I am going this route, luckily I do not need to use triggers on the table. Steve "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns961380405DD2Yazorman@127.0.0.1... > Steve (stevej@ufrmsa1.uniforum.org.za) writes: > > SCOPE_IDENTITY is not supported by SQL Server 7 (once again I am sorry I > > forgot to mention which version of > > SQL Server I am using) do you have any other suggestions ? > > Use @@identity instead. If you have a trigger on the table that inserts > into a secont identity table, @@identity will have the value from that > table. This is why scope_identity() is usually recommended, since it > returns the most recently used identity value in the current scope. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinf...2000/books.asp |
Linear Mode
