SET NOCOUNT ON
DECLARE @lastID BIGINT
-- Do your insert here.
INSERT INTO TEST
(foo)
VALUES
('hello')
-- This gets the value of the row you just inserted
SET @lastID = SCOPE_IDENTITY()
SET NOCOUNT OFF
-- Return back to CF.
SELECT @lastID AS lastID
</cfquery>
<cfoutput>#bar.lastID#</cfoutput>
----- Original Message -----
From: "Burns, John" <[EMAIL PROTECTED]>
Date: Thursday, March 11, 2004 11:02 am
Subject: Reliable way to get ID of inserted record
> I know this came up last week or so, but I believe the answer came
> for a
> mySQL database. I need a way in MS SQL to get the last inserted
> recordvia CF. I have a query inserting a row into the table with an
> auto-increment ID. I need to insert that ID into a separate table in
> another query so I first need to find out what ID it got assigned.
> I've
> got it all wrapped in <cftransaction> but I'm not sure if max(id)
> is the
> best way to get the ID, because if rows get deleted, won't SQL
> automatically assign those values to new rows at some point, therefore
> negating the max() idea?
>
> John Burns
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
