By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,161 Members | 2,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,161 IT Pros & Developers. It's quick & easy.

Is @@IDENTITY reliable?

P: n/a
SQL 7.0

I have a form in ASP.NET and I want to write the values to the SQL
Server tables.

The tables are Customer and Address tables.

There will be an insert into the Customer table, and I need to use the
Identity of this inserted record for the Foreign Key in the Address
table insert.

For example

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)

My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
[posted and mailed, please reply in news]

Edward (te********@hotmail.com) writes:
INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)
This syntax is not correct. You probably mean:

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @customerID = @@identity
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, @CustomerID)
My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?


The value of @@identity would normally come from the Customers table,
but there is one situation where it will not. This is if there is a
trigger on the Customers table, and this trigger too inserts data into
a table with the identity property. In that case, @@identity will have
the identity value in that table. (And if you are running SQL7 without
service packs, there is an ugly bug which clears @@identity even if
the triggers inserts into an IDENTITY-less table.)

In SQL2000 there exists an alternative, the function scope_identity(),
which returns the most recently generated IDENTITH value in the current
scope (that is current procedure, trigger etc.)

Note that the value of @@identity *is* local to the current connection,
so it cannot be affected by concurrent operations on other connections.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a
@@IDENTITY will return the last identity value generated on the current
database connection. The value will not be affected by concurrent inserts
on other connections. However, an issue with @@IDENTITY in SQL 7 is that
the value can get changed my an insert trigger that inserts into tables with
identity columns. In SQL 2000, you can use SCOPE_IDENTIT(), which is not
affected by triggers.

BTW, your SQL code will need to specify @@IDENTITY or other variable on the
insert statement. You need to do something like:

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

SE:LECT @CustomerID = @@IDENTITY

INSERT INTO Address (Address, CustomerID)
VALUES (@Address, @CustomerID)
--
Hope this helps.

Dan Guzman
SQL Server MVP
"Edward" <te********@hotmail.com> wrote in message
news:25**************************@posting.google.c om...
SQL 7.0

I have a form in ASP.NET and I want to write the values to the SQL
Server tables.

The tables are Customer and Address tables.

There will be an insert into the Customer table, and I need to use the
Identity of this inserted record for the Foreign Key in the Address
table insert.

For example

INSERT INTO Customer (CustomerName)
VALUES (@CustomerName)

Select @@identity as CustomerID
INSERT INTO Address (Address, CustomerID)
VALUES (@Address, CustomerID)

My question is this. If I put this into a single stored procedure can
I absolutely GUARANTEE that the @@identity value will be from the
Customer table insert, or could it feasibly be from another, as it
were, colliding operation?

TIA

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.