Here's what I use for a parent child relationship, both have an Identity PK:
DECLARE @Ident Int
DECLARE @AdItemIdent Int
SET NOCOUNT ON
INSERT INTO AdSummary
(
CreateDateTime,
CustomerID
)
VALUES
(
#NOW()#
#user.CustomerID#
)
SET @Ident = @@IDENTITY -- Det the PK just assigned
INSERT INTO AdItem
(
CreateDateTime,
AdSummaryID
)
VALUES
(
#NOW()#,
@Ident -- Set the FK to Parent's PK
)
SET @AdItemIdent = @@IDENTITY
SELECT NewAdItemId = @AdItemIdent -- Make Child PK visible
SET NOCOUNT OFF
HTH
Dick
At 12:33 PM -0500 7/20/01, Pooh Bear wrote:
>ok guys, please tell me if there is a better way of doing this.
>
>basically i have 2 tables, the second one having a many to many
>relationship. What I am trying to do is insert a record into the first
>table, then use the id (it's an identity field) of that row to insert into
>the second table with some other information.
>
>The way I am doing it now is having coldfusion insert the row, then do
>another query to find the id of that row using a criteria such as email
>(which is unique anyways). After i find the id, I do another insert into
>the second row and enter in that id along with other information.
>
>My goal is to skip that second part, where i have to do another query just
>to get the id. is there a way to get the id automatically after it's been
>created or something like that? I might go the route of having the DB
>return the max id created, but I am paranoid that before it even starts to
>get the id, another record has already been inserted, and we lose
>consistency or somethin. dunno, i know u guys do this every day so how do u
>do it?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
