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.
| Author |
Topic |
|
feejaz
Yak Posting Veteran
68 Posts |
 Posted - 2008-05-16 : 08:27:56
|
| Hi All,Is there any option to get the velue of auto increment number before inserting record.My problem is that I want to get the auto increment number, for this I am using MaxId function, but If i have deleted the some rows from table, I could not get the actual.For example there are 20record s in the table I have delete the last 3 records now I have the last value of Identity is 17. When I used the Maxid function It gives me the 18 number. But I need 21.How?help required.ThanksNavi |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-16 : 08:33:08
|
| Do you need it before the insert? You can use scope_identity() to get the actual identity value imediately after the insert.Be One with the OptimizerTG |
 |
|
|
feejaz
Yak Posting Veteran
68 Posts |
Posted - 2008-05-16 : 08:38:28
|
| Thanks for repling,Yes I need it before inserting the record. Because I want to use this number to the another table as a forign key. Actully , I am going to inserting the record Group in the three tables.ThanksNavi |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-16 : 08:53:16
|
| You should either pass all the info needed to a single SP which will handle inserts to all tables or make multiple calls. The first call does the insert to the parent table and retrieves the identity value and subsequent calls uses the identity in the other tables.The problem with retrieve a value first then using it is that concurrent users will collide. The value you derive in preperation to use could get used out from under you by another user.Be One with the OptimizerTG |
|
|
|
feejaz
Yak Posting Veteran
68 Posts |
Posted - 2008-05-16 : 09:32:22
|
| Sorry I could not understand,You mean I could not get the actual Identity.Navi |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-16 : 10:01:19
|
| Yes, if you use any method to "figure out" what the next identity value will be, it may not be the identity value that you actually get when you do the insert. This could happen when more than one user is trying to insert at about the same time. It is possible to prevent that by custom locking or manipulating the transaction isolation level, but the standard way of avoiding that problem is to use SCOPE_IDENTITY() to get the actual value and then use that value in subsequent inserts to child tables.Be One with the OptimizerTG |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-05-16 : 10:59:45
|
| This will get you what you're asking for...http://redlike.blogspot.com/2007/06/determining-next-identity-value-for.htmlBUT, TG is right. You should think about implications of your process running twice, simultaneously. That could cause something to go wrong. The usual way to do things, and to avoid that issue, is to get the identity value only after the insert, using SCOPE_IDENTITY(). This is what TG is telling you...Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|
|
Get the deleted auto Increment Number.