473,378 Members | 1,346 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Is @@IDENTITY reliable?

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
2 4642
[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
@@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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Phil W | last post by:
Hi all, Am having a bit of trouble with the @@identity field - I probably just have that friday feeling and am missing off something obvious, but the below code brings back am empty identity...
112
by: Andy | last post by:
Hi All! We are doing new development for SQL Server 2000 and also moving from SQL 7.0 to SQL Server 2000. What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000? Please,...
17
by: Trevor Best | last post by:
I don't know if this has been reported before but it appears to be a bug with Access. If I create two tables both with an identity column then create an insert trigger on table1 that inserts a...
3
by: Brian Conway | last post by:
Does anyone know how I can assign a Session variable to this? so that I can pull it back out on another page?
6
by: Hardy Wang | last post by:
Hi all, I have the following codes, but SCOPE_IDENTITY() just returns NULL to me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works fine!! Since I have a trigger on the...
37
by: spam.noam | last post by:
Hello, Guido has decided, in python-dev, that in Py3K the id-based order comparisons will be dropped. This means that, for example, "{} < " will raise a TypeError instead of the current...
41
by: pb648174 | last post by:
In a multi-user environment, I would like to get a list of Ids generated, similar to: declare @LastId int select @LastId = Max(Id) From TableMania INSERT INTO TableMania (ColumnA, ColumnB)...
11
by: OlafMeding | last post by:
Because of multithreading semantics, this is not reliable. This sentence is found in the Python documentation for "7.8.1 Queue Objects". This scares me! Why would Queue.qsize(), Queue.empty(...
7
by: Cindy H | last post by:
Hi I'm having a problem getting the insert statement correct for an Access table I'm using. The Access table uses an autonumber for the primary key. I have tried this: INSERT INTO Tournaments...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.