Using ID from an Inserted record in a second insert

Using ID from an Inserted record in a second insert

Post by dGQ » Wed, 08 Feb 2006 11:51:27


I have two tables, tblRequests and tblProcesses which are linked via
RequestID. In a Stored Procedure I want to insert two records, one into the
main table tblRequests, get the RequestID and use the RequestID to insert a
row in the second table tblProcess all within a single transaction. How do
you get a value back from the first INSERT for use in the second INSERT
please?
TIA
td
 
 
 

Using ID from an Inserted record in a second insert

Post by Louis Davi » Wed, 08 Feb 2006 12:50:23

Two ways (assuming that you are using identity values, otherwise just one)

insert requests (<columns other than requestId>)
values (@<variables other than @requestId>)

declare @requestId int

--method 1
set @requestId = scope_identity()

--method 2, if you have an instead of trigger or you have some other code
building the key

select @requestId = requestId
from requests
where alternateKeyColumn1 = @akColumn1FromInsert
and ...

For every table that you use an identity column, then you very much need to
have another UNIQUE constraint, otherwise you will have a likelihood of
duplicating important data (not to mention proving Mr Celko right :)

For example, say you have a table of names with an identity column:

NameId Name
1 Joe
2 Joe
3 Joe

In this case you don't have a key, you have a record locator, because the
only way to tell the rows apart is their position in the table, which is not
what should be done 99.9% of the time (sometimes there is no natural key to
be found, but it is really really rare.)

--
----------------------------------------------------------------------------
Louis Davidson - http://www.yqcomputer.com/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)

 
 
 

Using ID from an Inserted record in a second insert

Post by dGQ » Wed, 08 Feb 2006 13:41:43

Thank you very much.
td
 
 
 

Using ID from an Inserted record in a second insert

Post by Dave Markl » Wed, 08 Feb 2006 13:50:44

INSERT INTO tblRequests ...

DECLARE @RequestID INT
SET @RequestID = SCOPE_IDENTITY()

INSERT INTO tblProcesses(RequestID, ...) VALUES (@RequestID ...)

-Dave
 
 
 

Using ID from an Inserted record in a second insert

Post by dGQ » Thu, 09 Feb 2006 06:16:28

Thanks Dave
==============================