Newbie-- sp with multiple record insert: how to get all inserted identities -- sql server (alternate)

David Portas
11/13/2004 9:20:36 AM

Use your table's natural key to retrieve the IDENTITY value. You don't say
just where the data comes from that you are inserting but assuming it comes
from another table your SP might look something like this:

/* Insert the rows */
INSERT INTO T2 (key_col, col1, col2, col3, ...)
SELECT key_col, col1, col2, col3, ...
FROM T1
WHERE T1...

/* retrieve the identity values */
SELECT T2.id
FROM T1
JOIN T2
ON T1.key_col = T2.key_col /* the key */
WHERE T1...

If you don't have a key other than IDENTITY then you have big problems...
but hopefully you know that anyway. IDENTITY should never be the only key of
a table.

BTW in SQL Server 2000 don't use @@IDENTITY to retrieve the last inserted
IDENTITY value, use SCOPE_IDENTITY instead. SCOPE_IDENTITY is more reliable
because it is scoped to your SP whereas @@IDENTITY isn't.

--
David Portas
SQL Server MVP
--

daddygiles NO[at]SPAM yahoo.com
11/13/2004 8:42:42 PM

This worked perfectly! Thanks very much!

[quoted text, click to view]

Erland Sommarskog
11/13/2004 10:49:10 PM

[posted and mailed, please reply in news]

Chris G (daddygiles@yahoo.com) writes:
[quoted text, click to view]

Maybe you should reconsider using the IDENTITY property at all. IDENTITY
sometimes comes in handy, but there are also situations where it causes
problems and this is one of them.

First thing to consider is whether you need an artificial primary key or
not. If there is a natural key in the data, use that key. This includes
the case, you combine data from two tables. If you have Products and
Warehouses, and a product can be in more than one warehouse, and you
have a ProductWarehouse table, the key of that table should be (ProductID,
WarehouseID), and not some meaningless IDENTITY key.

But not all data is well-formed enough to have a key that fulfils the
requirement of a primary key in a relational database. In this case, an
artificial key becomes necessary. But whether it should be an IDENTITY or
not, depends. For this particular situation, a good strategy is this:

CREATE TABLE #temp (ident int IDENTITY,
col1 ...
col2 ...)

INSERT #temp
SELECT ... -- Whereever you insert the data from today.

BEGIN TRANSCATION

-- Get next available id in target table. UPDLOCK is needed to prevent
-- some other process from getting the same key.
SELECT @id = coalesce(MAX(id), 0) + 1 FROM tbl (UPDLOCK)

INSERT target (id, col1, col2, ...)
SELECT @id + ident, col1, col2, ...
FROM #temp

-- Further processing follows.
...

COMMIT TRANSACTION

Here you use IDENTITY, but in a temp table to give you the numbers to
add to @id. This permits you easily retrieve the key value for each
inserted row.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Newbie-- sp with multiple record insert: how to get all inserted identities