House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Talk (CF-Talk)

Fetching last record

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Ken,
Nick Tong - TalkWebSolutions.co.uk
06/02/06 09:33 A
Hey folks,
Joe Rinehart
06/02/06 09:56 P
That sure worked. Thanks guys.
Ken
06/02/06 10:53 A
AFAIK you have to do two queries.
Josh Nathanson
06/02/06 05:11 P
> AFAIK you have to do two queries.
Denny Valliant
06/02/06 07:50 P
You have several options open to you....
Robertson-Ravo, Neil (RX)
06/02/06 09:27 A
As noted, there is no real problem/benefit with either
Robertson-Ravo, Neil (RX)
06/02/06 12:07 P
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Ken
06/02/2006 09:22 AM

Hi. I am doing a Insert statement into my db, and immediately after the Insert I need to fetch the value of the field ID (Identity). I know I could do a Select Max(ID), but how else can I fetch the value of the last inserted record? DB: SQL 2000. Please help. - Ken.

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Ryan Guill
06/02/2006 09:26 AM

A lot of times, I put a field in my database that is the time inserted.  Then what you can do is set a varaible before your insert with the time you are going to insert, then do your insert using that time, and then you can do a select with that time right after it.  If you are using a timestamp field, the only way you should get a bad record is if you had two inserts into that table at exactly the same millisecond, and if you are worried about that, use another field that you inserted as well in your where clause. ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Nick Tong - TalkWebSolutions.co.uk
06/02/2006 09:33 AM

Ken, <cfquery datasource="yourdsn" name="myInsert">     INSERT INTO myable (columns)     VALUES(values)     SELECT @@IDENTITY AS lastID </cfquery> <cfoutput>#myinsert.lastID#</cfoutput> ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Joe Rinehart
06/02/2006 09:56 PM

Hey folks, On MSSQL, using @@IDENTITY is a bad idea.  Use SCOPE_IDENTITY() instead.  Google "@@IDENTITY" and you'll get plenty of details as to why. -Joe ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Peter Legg
06/02/2006 11:51 AM

You should use SCOPE_IDENTITY instead of @@IDENTITY. Peter >That sure worked. Thanks guys. > >- Ken

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Michael E. Carluen
06/02/2006 03:27 PM

Is there a mySQL equivalent to SCOPE_IDENTITY and @@IDENTITY?  Thanks. You should use SCOPE_IDENTITY instead of @@IDENTITY. Peter >That sure worked. Thanks guys. > >- Ken > > >>

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Josh Nathanson
06/02/2006 03:33 PM

Yup, LAST_INSERT_ID(), or to be more careful, MAX(LAST_INSERT_ID()) Although someone will probably say why it's not exactly equivalent. --Josh ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Michael E. Carluen
06/02/2006 04:04 PM

Thanks Josh.  Still a 2-query transaction, huh?... first to insert, second to select.(?) 'Would be great if mySQL can do these 2 on a single query like MSSQL2k. Yup, LAST_INSERT_ID(), or to be more careful, MAX(LAST_INSERT_ID()) Although someone will probably say why it's not exactly equivalent. --Josh ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Josh Nathanson
06/02/2006 05:11 PM

AFAIK you have to do two queries. -- Josh ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Robertson-Ravo, Neil (RX)
06/02/2006 09:27 AM

You have several options open to you.... SCOPE_IDENTITY @@IDENTITY IDENT_CURRENT SCOPE_IDENTITY and @@IDENTITY have different uses. There is no benefit to using one over the other. They both return the last value inserted into an Identity column. However, the logic they use to determine the value is different. Take this scenario: You have 2 tables, Table1 and Table2. An insert into Table1 triggers an insert into Table2 via an Insert trigger. @@INDENTITY will return the value of the IDENTITY in Table1, SCOPE_IDENTITY will return the value of the Identity in Table2. This is because SCOPE_IDENTITY is limited to the current scope, while @@IDENTITY is limited to the current session. @@IDENTITY then returns the IDENTITY value from Table2. In a scenario where you are inserting into a single table, they will both return the same value. Of course, if you are inserting itno related tables in a transaction, or there are Insert triggers involved, you need to be careful you get the value from the correct table! For the sake of discussion, there is also an IDENT_CURRENT. It limits itself to a particular table across all scopes. It is used like this: IDENT_CURRENT('table_name') In summary: IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. HTH N Hi. I am doing a Insert statement into my db, and immediately after the Insert I need to fetch the value of the field ID (Identity). I know I could do a Select Max(ID), but how else can I fetch the value of the last inserted record? DB: SQL 2000. Please help. - Ken.

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Robertson-Ravo, Neil (RX)
06/02/2006 12:07 PM

As noted, there is no real problem/benefit with either You should use SCOPE_IDENTITY instead of @@IDENTITY. Peter >That sure worked. Thanks guys. > >- Ken > > >>


<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

October 31, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
       1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31   

Designer, Developer and mobile workflow conference