469,648 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

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
Jul 23 '05 #1
8 5830
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
--

Jul 23 '05 #2
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" <RE****************************@acm.org> wrote in message
news:11*********************@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
--

Jul 23 '05 #3
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" <RE****************************@acm.org> wrote in message
news:11*********************@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
--

Jul 23 '05 #4

Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan

Jul 23 '05 #5
Madhivanan (ma************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Steve (st****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
Thanks, but I have to agree with Erland

Steve
"Madhivanan" <ma************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Steve,

Select Max(IdentityField)+1 from TableName

Madhivanan

Jul 23 '05 #8
Thanks

I am going this route, luckily I do not need to use triggers on the table.

Steve

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Steve (st****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

24 posts views Thread by Ilija_G | last post: by
reply views Thread by Gianfranco | last post: by
7 posts views Thread by Brian | last post: by
1 post views Thread by Tempera | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.