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 record via 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]
RE: Reliable way to get ID of inserted record
As far as I know, whatever the maxid is at the time you will get next MaxID +1. However if you are deleting the last record and then adding a new one, In SQLServer you will get the following. ID Value 1 a 2 b 3 c 4 d ID Value 1 a 2 b 3 c 4 d --Remove ID Value 1 a 2 b 3 c add a new record ID Value 1 a 2 b 3 c 5 d -Original Message- From: Burns, John [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 12:03 PM To: CF-Talk 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 record via 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]
Re: Reliable way to get ID of inserted record
Do your insert - and then do a select looking for a record matching several of the fields you just inserted. Doing a max(id) isn't 100% sure w/out transactional processing. But if you select on several fields that you *know* are unique... or at least, take the top record while sorting by the id field descending, that should be pretty safe. Only if you added two fields back to back with identical data would that hiccup on you. At 11:02 AM 3/11/2004, you wrote: 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 record via 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]
Re: Reliable way to get ID of inserted record
On Thu, 2004-03-11 at 10:02, Burns, John wrote: 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 record via 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, if you have no intention of ever moving off of MSSQL you can use the variable @@identity to get the last inserted it. For example: cfquery name=idtest ... set nocount on insert into my_table ( test ) values ( 'hi there' ) select @@identity as newid set nocount off /cfquery cfdump var=#idtest# If you think you might move off of MSSQL I wouldnt do that. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Reliable way to get ID of inserted record
But won't it eventually come back in and fill in some of those ones that were previously deleted?As in your example, at some point won't another item get assigned the value of 4? John Burns -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 1:13 PM To: CF-Talk Subject: RE: Reliable way to get ID of inserted record As far as I know, whatever the maxid is at the time you will get next MaxID +1. However if you are deleting the last record and then adding a new one, In SQLServer you will get the following. ID Value 1 a 2 b 3 c 4 d ID Value 1 a 2 b 3 c 4 d --Remove ID Value 1 a 2 b 3 c add a new record ID Value 1 a 2 b 3 c 5 d -Original Message- From: Burns, John [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 12:03 PM To: CF-Talk 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 record via 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]
RE: Reliable way to get ID of inserted record
Not a SQL Server guy, but can't you use @@IDENTITY.I believe there are three different forms, which do slightly different things (global last identity, connection last identity, and database last identity, or something like that).Check the docs. Cheers, barneyb -Original Message- From: Burns, John [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 10:03 AM To: CF-Talk 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 record via 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]
RE: Reliable way to get ID of inserted record
On Thu, 2004-03-11 at 10:16, Burns, John wrote: But won't it eventually come back in and fill in some of those ones that were previously deleted?As in your example, at some point won't another item get assigned the value of 4? MSSQL servers identity columns just keep going untill it they fill up a signed 32 bit integer (I believe might even be larger 2,147,483,648), at that point it flips around and starts over at 1. So unless you think you'll get that many request between inserts - which would be rad! - you should be fine. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Reliable way to get ID of inserted record
After reading the other responses, I have often selected the identity back out, but you can also use a trigger if you're sticking to MS SQL.This is my preferred way of dealing with this. -Original Message- From: Burns, John [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 1:03 PM To: CF-Talk 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 record via 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]
Re: Reliable way to get ID of inserted record
Be carefull using @@IDENTITY, as it can return erroneous information (especially if your tables have triggers associated with them).If you are using SQL Server 2000, use the SCOPE_IDENTITY() function instead of @@IDENTITY. Cheers, Jeff Garza - Original Message - From: Rob To: CF-Talk Sent: Thursday, March 11, 2004 11:32 AM Subject: RE: Reliable way to get ID of inserted record On Thu, 2004-03-11 at 10:16, Burns, John wrote: But won't it eventually come back in and fill in some of those ones that were previously deleted?As in your example, at some point won't another item get assigned the value of 4? MSSQL servers identity columns just keep going untill it they fill up a signed 32 bit integer (I believe might even be larger 2,147,483,648), at that point it flips around and starts over at 1. So unless you think you'll get that many request between inserts - which would be rad! - you should be fine. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Reliable way to get ID of inserted record
From: Jeff Garza [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 1:35 PM Subject: Re: Reliable way to get ID of inserted record Be carefull using @@IDENTITY, as it can return erroneous information (especially if your tables have triggers associated with them).If you are using SQL Server 2000, use the SCOPE_IDENTITY() function instead of @@IDENTITY. Oooh! Use it in an example please! And how exactly *can* this produce erroneous info? Jeff, always used @@IDENTITY...and is now scurred... [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Reliable way to get ID of inserted record
On Thu, 2004-03-11 at 10:35, Jeff Garza wrote: Be carefull using @@IDENTITY, as it can return erroneous information (especially if your tables have triggers associated with them).If you are using SQL Server 2000, use the SCOPE_IDENTITY() function instead of @@IDENTITY. It doesnt return erroneous information - you just have to know how it works. I have never had a problem with it, but I guess thats because I knew the rule. @@identity returns the last inserted columns identity within the transaction - key being on the last inserted - no matter where the insert took place. I'd never heard of scop_identity() till just now, but that sounds like a nice function - thanks Jeff ... @@Identity returns the most recently created identity for your current connection. When you first use it, it might be fine. Until someone adds a trigger. If the trigger causes another identity to be created, guess which identity you'll get in your call to @@Identity? Not nice. scope_identity() is much nicer. It gives you what you're expecting. http://weblogs.asp.net/rosherove/archive/2003/11/13/37217.aspx -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Reliable way to get ID of inserted record
btw scope_identity() is not supported in SqlServer 7. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Reliable way to get ID of inserted record
But at the point where it does flip around, when you grabbed max() that would no longer be the newest one right? John -Original Message- From: Rob [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 1:33 PM To: CF-Talk Subject: RE: Reliable way to get ID of inserted record On Thu, 2004-03-11 at 10:16, Burns, John wrote: But won't it eventually come back in and fill in some of those ones that were previously deleted?As in your example, at some point won't another item get assigned the value of 4? MSSQL servers identity columns just keep going untill it they fill up a signed 32 bit integer (I believe might even be larger 2,147,483,648), at that point it flips around and starts over at 1. So unless you think you'll get that many request between inserts - which would be rad! - you should be fine. -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Reliable way to get ID of inserted record
On Thu, 2004-03-11 at 10:51, Burns, John wrote: But at the point where it does flip around, when you grabbed max() that would no longer be the newest one right? Yes. That is correct. You might want to double check me on that behavoir (I dont have access to ms docs at the moment, but I did some reseach on this about a year ago and I am 95% sure that is the behavoir). but last time I looked 1 2147483648 heheheh -- Rob [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Reliable way to get ID of inserted record
cfquery name=bar datasource=yours
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]
Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours.This is only an issue where you have a trigger that inserts into a different table that has an IDENTITY field in it.In that case, @@IDENTITY will return the inserted ID from the trigger rather than your your original insert.Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL Server 2000...
I use it because our DBA has a habit of adding triggers to our tables without our knowledge... been burned once by it before.
As for actual usage, it's pretty much the same as @@IDENTITY:
TABLE DESIGN
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
SQL QUERY
SET NOCOUNT ON
INSERT INTO myTable (othercol)
VALUES ('myvalue')
SELECT SCOPE_IDENTITY() AS newID
SET NOCOUNT OFF
This returns queryname.newID to CF...
Cheers,
Jeff Garza
- Original Message -
From: Jeff Small
To: CF-Talk
Sent: Thursday, March 11, 2004 11:48 AM
Subject: Re: Reliable way to get ID of inserted record
From: Jeff Garza [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 1:35 PM
Subject: Re: Reliable way to get ID of inserted record
Be carefull using @@IDENTITY, as it can return erroneous information
(especially if your tables have triggers associated with them).If you are
using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
@@IDENTITY.
Oooh! Use it in an example please! And how exactly *can* this produce
erroneous info?
Jeff,
always used @@IDENTITY...and is now scurred...
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours.This is only an issue where you have a trigger that inserts into a different table that has an IDENTITY field in it.In that case, @@IDENTITY will return the inserted ID from the trigger rather than your your original insert.Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL Server 2000... I use it because our DBA has a habit of adding triggers to our tables without our knowledge... been burned once by it before. As for actual usage, it's pretty much the same as @@IDENTITY: Yeah, I thought Rob did a pretty good job of explaining it. I had never messed with it, but the bit about triggers kinda worried me because of that very issue that I was afraid of. I'm going to play with it later and will probably be replacing my @@IDENTITY with the SCOPE_IDENTITY() since we're only using SQL Server 2000 right now and in the foreseeable future. Thanks. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Reliable way to get ID of inserted record
Can scope_identity() be used to get identities in a query such as the
piece I have below? I tried using Select Scope_Identity() AS emailID but
my query kept complaining that emailID didn't exist.
SET NOCOUNT ON
DECLARE @thisID int
DECLARE @emailID int
BEGIN transaction
/* assume this table holds main key
to insert into other tables*/
INSERT INTO sample_login
(
emailAddress
)
VALUES
(
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.emailAddress# /
)
SELECT @emailID = @@IDENTITY
INSERT INTO sample_customer_info
(
emailFK,
CustName,
CustCompany,
CustAddress1,
CustAddress2,
CustCity,
CustState,
CustZip,
CustCountry,
CustPhone,
CustFax,
CustWebsite
)
VALUES
(
emailID,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustName# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCompany# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress1# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress2# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCity# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustState# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustZip# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCountry# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustPhone# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustFax# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustWebsite# /
)
Ben
-Original Message-
From: Jeff Garza [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 3:13 PM
To: CF-Talk
Subject: Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours.This is
only an issue where you have a trigger that inserts into a different
table that has an IDENTITY field in it.In that case, @@IDENTITY will
return the inserted ID from the trigger rather than your your original
insert.Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL
Server 2000...
I use it because our DBA has a habit of adding triggers to our tables
without our knowledge... been burned once by it before.
As for actual usage, it's pretty much the same as @@IDENTITY:
TABLE DESIGN
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
SQL QUERY
SET NOCOUNT ON
INSERT INTO myTable (othercol)
VALUES ('myvalue')
SELECT SCOPE_IDENTITY() AS newID
SET NOCOUNT OFF
This returns queryname.newID to CF...
Cheers,
Jeff Garza
- Original Message -
From: Jeff Small
To: CF-Talk
Sent: Thursday, March 11, 2004 11:48 AM
Subject: Re: Reliable way to get ID of inserted record
From: Jeff Garza [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 1:35 PM
Subject: Re: Reliable way to get ID of inserted record
Be carefull using @@IDENTITY, as it can return erroneous information
(especially if your tables have triggers associated with them).If
you are
using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
@@IDENTITY.
Oooh! Use it in an example please! And how exactly *can* this produce
erroneous info?
Jeff,
always used @@IDENTITY...and is now scurred...
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Reliable way to get ID of inserted record
Nevermind, I got it.
Thanks,
Ben
-Original Message-
From: Ben Densmore
Sent: Thursday, March 11, 2004 3:53 PM
To: CF-Talk
Subject: RE: Reliable way to get ID of inserted record
Can scope_identity() be used to get identities in a query such as the
piece I have below? I tried using Select Scope_Identity() AS emailID but
my query kept complaining that emailID didn't exist.
SET NOCOUNT ON
DECLARE @thisID int
DECLARE @emailID int
BEGIN transaction
/* assume this table holds main key
to insert into other tables*/
INSERT INTO sample_login
(
emailAddress
)
VALUES
(
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.emailAddress# /
)
SELECT @emailID = @@IDENTITY
INSERT INTO sample_customer_info
(
emailFK,
CustName,
CustCompany,
CustAddress1,
CustAddress2,
CustCity,
CustState,
CustZip,
CustCountry,
CustPhone,
CustFax,
CustWebsite
)
VALUES
(
emailID,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustName# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCompany# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress1# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress2# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCity# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustState# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustZip# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCountry# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustPhone# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustFax# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustWebsite# /
)
Ben
-Original Message-
From: Jeff Garza [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 3:13 PM
To: CF-Talk
Subject: Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours.This is
only an issue where you have a trigger that inserts into a different
table that has an IDENTITY field in it.In that case, @@IDENTITY will
return the inserted ID from the trigger rather than your your original
insert.Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL
Server 2000...
I use it because our DBA has a habit of adding triggers to our tables
without our knowledge... been burned once by it before.
As for actual usage, it's pretty much the same as @@IDENTITY:
TABLE DESIGN
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
SQL QUERY
SET NOCOUNT ON
INSERT INTO myTable (othercol)
VALUES ('myvalue')
SELECT SCOPE_IDENTITY() AS newID
SET NOCOUNT OFF
This returns queryname.newID to CF...
Cheers,
Jeff Garza
- Original Message -
From: Jeff Small
To: CF-Talk
Sent: Thursday, March 11, 2004 11:48 AM
Subject: Re: Reliable way to get ID of inserted record
From: Jeff Garza [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 1:35 PM
Subject: Re: Reliable way to get ID of inserted record
Be carefull using @@IDENTITY, as it can return erroneous information
(especially if your tables have triggers associated with them).If
you are
using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
@@IDENTITY.
Oooh! Use it in an example please! And how exactly *can* this produce
erroneous info?
Jeff,
always used @@IDENTITY...and is now scurred...
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Reliable way to get ID of inserted record
Ben,
In this situation you would want to use your @emailID variable to hold the value of Scope_Identity.
So something like the following should work...
SET @emailID = SCOPE_IDENTITY()
Instead of
Select Scope_Identity() AS emailID
HTH,
Jeff Garza
- Original Message -
From: Ben Densmore
To: CF-Talk
Sent: Thursday, March 11, 2004 1:52 PM
Subject: RE: Reliable way to get ID of inserted record
Can scope_identity() be used to get identities in a query such as the
piece I have below? I tried using Select Scope_Identity() AS emailID but
my query kept complaining that emailID didn't exist.
SET NOCOUNT ON
DECLARE @thisID int
DECLARE @emailID int
BEGIN transaction
/* assume this table holds main key
to insert into other tables*/
INSERT INTO sample_login
(
emailAddress
)
VALUES
(
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.emailAddress# /
)
SELECT @emailID = @@IDENTITY
INSERT INTO sample_customer_info
(
emailFK,
CustName,
CustCompany,
CustAddress1,
CustAddress2,
CustCity,
CustState,
CustZip,
CustCountry,
CustPhone,
CustFax,
CustWebsite
)
VALUES
(
emailID,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustName# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCompany# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress1# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustAddress2# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCity# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustState# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustZip# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustCountry# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustPhone# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustFax# /,
cfqueryparam
cfsqltype=CF_SQL_LONGVARCHARvalue=#memento.CustWebsite# /
)
Ben
-Original Message-
From: Jeff Garza [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 3:13 PM
To: CF-Talk
Subject: Re: Reliable way to get ID of inserted record
I think that Rob nailed it in the posting right after yours.This is
only an issue where you have a trigger that inserts into a different
table that has an IDENTITY field in it.In that case, @@IDENTITY will
return the inserted ID from the trigger rather than your your original
insert.Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL
Server 2000...
I use it because our DBA has a habit of adding triggers to our tables
without our knowledge... been burned once by it before.
As for actual usage, it's pretty much the same as @@IDENTITY:
TABLE DESIGN
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
SQL QUERY
SET NOCOUNT ON
INSERT INTO myTable (othercol)
VALUES ('myvalue')
SELECT SCOPE_IDENTITY() AS newID
SET NOCOUNT OFF
This returns queryname.newID to CF...
Cheers,
Jeff Garza
- Original Message -
From: Jeff Small
To: CF-Talk
Sent: Thursday, March 11, 2004 11:48 AM
Subject: Re: Reliable way to get ID of inserted record
From: Jeff Garza [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Thursday, March 11, 2004 1:35 PM
Subject: Re: Reliable way to get ID of inserted record
Be carefull using @@IDENTITY, as it can return erroneous information
(especially if your tables have triggers associated with them).If
you are
using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
@@IDENTITY.
Oooh! Use it in an example please! And how exactly *can* this produce
erroneous info?
Jeff,
always used @@IDENTITY...and is now scurred...
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Reliable way to get ID of inserted record
From: Ben Densmore Can scope_identity() be used to get identities in a query such as the piece I have below? I tried using Select Scope_Identity() AS emailID but my query kept complaining that emailID didn't exist. You're within a SET NOCOUNT ON, so it won't be output to the query Make sure you do the SELECT @field AS NewID outside of the NOCOUNT block and all should be well [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Reliable way to get ID of inserted record
I use the CF_MAXID function.
Dwayne Cole, MS in MIS, MBA
Certified Advanced ColdFusion Developer
850-591-0212
It can truly be said that nothing happens until there is vision. But it is equally true that a vision with no underlying sense of purpose, no calling, is just a good idea - all sound and fury, signifying nothing.The Fifth Discipline - Peter Senge
-- Original Message --
From: Kwang Suh [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date:Thu, 11 Mar 2004 11:19:48 -0700
cfquery name=bar datasource=yours
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]
