returning identity values when inserting multiple rows

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

returning identity values when inserting multiple rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-15 : 08:45:57

Jim writes "Hello.

When inserting a single row into a table which includes an IDENTITY column, retrieving the identity value of the inserted row is simple- using @@IDENTITY.

However, doing the same thing when inserting multiple rows seems to be much more complex.

For example, I have a stored procedure that builds a temporary table (#Inserts) of values which it is going to insert into a permanent table with an identity column (tblProducts). It inserts these rows and then needs to retreive the identity values for all the rows just inserted and update the temporary table with them, so that these identity values can be used as foreign keys in other table inserts.

tblProducts is defined as follows:

CREATE TABLE tblProducts (
ProductID INT NOT NULL IDENTITY(1, 1),
ProductName VARCHAR(100)
)

ProductName is not neccesarily unique so ProductID is the primary (and only) key on the table. Also, I hoped to find a solution that avoids triggers as the action which needs to be done next depends upon the context in which the rows were inserted and also i find it limiting that you cannot use CREATE TABLE statements within triggers.

The best solution that I have come up with is as follows:

-- define the temp table of rows to be inserted

CREATE TABLE #Inserts (
ProductID INT,
TempID INT NOT NULL IDENTITY(1, 1),
ProductName VARCHAR(100)
)

-- add content to temp table

INSERT INTO #Inserts (ProductName)
SELECT 'Cornflakes'
UNION ALL
SELECT 'Rice Crsipies'
UNION ALL
.... etc etc

-- insert rows from #Inserts into tblProducts

DECLARE @FirstIdentity INT

INSERT INTO tblProducts (ProductName)
SELECT ProductName
FROM #Inserts
ORDER BY TempID

SET @FirstIdentity = @@IDENTITY - @@ROWCOUNT

-- update rows in #Inserts with the IDENTITY values just inserted

UPDATE #Inserts
SET ProductID = TempID + @FirstIdentity

----------------------------

@FirstIdentity is the ProductID of the first row inserted minus one, so adding @FirstIdentity to TempID in each row should yield the actual ProductID of the inserted row.

This *seems* to work, but I am worried that there may be problems with it:

Can I rely on the the ProductIDs always being sequentially numbered? Could other processes simultaneously inserting into tblProducts produce unpredictable results? Is there some level of transaction serialisation which would prevent such unpredictability?

I am using SQL Server 7 running on Windows NT.

I hope what I've written above makes some kind of sense. And I hope you can help me.

Cheers,

Jim Dummett"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources