Get last Uniqueidentifier inserted in SQL Server table

[BACFL]

I am looking for code that will get me the last entered ID in a table.  I am using SQL Server and have the ID's set as uniqueidentifiers.  I tried SCOPE_IDENTITY() and had an issue.  Also, want to make sure it is for that unique insert, not an insert that slipped in almost concurrently.

Thanks!

BCo

[saurav Pandit]

Hi,

You can use result attribute of cfquery tag to get the id of last inserted record.

<cfquery name="testqry" datasource="somesource" result="restestqry">

    write query for insertion     

</cfquery>

<cfdump var="#restestqry#">

you will see the last inserted record id in dump result.

For details please look at documentation of coldfusion.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Part_3_CFML_Ref_1.html

Thanks

saurav

[BACFL]

Thanks for the answer, but I think I am looking for something without using CFDUMP and is in the SQL Statement.

[BACFL]

Alternately, if I use <cftransaction> around the input and the select queries am I guaranteed to get the last ID?

Thanks again!

B.

[Dan_Bracuk]

The suggestion for using cfdump was to show you what information was available and to give you a hint as to the name of the variable with the information you seek.  It was not intended for your production code.

Regarding your other post, cftransaction will do some things for you, but guaranteeing that you get the correct record ID is not one of them.

[-__cfSearching__- · Accepted solution]

I am looking for code that will get me the last entered ID in a table.  I am using SQL Server and have the ID's set as uniqueidentifiers.  I tried SCOPE_IDENTITY() and had an issue.  Also, want to make sure it is for that unique insert, not an insert that slipped in almost concurrently.

SCOPE_IDENTITY() only works with identity columns.  If your table does not contain an identity column that function will be of no use to you. Neither will cfquery's "result" attribute as it only works with ms sql identity values too.

AFAIK, there is no built in method for automatically returning a newly created uniqueidentifier.  The closest you can get in MS SQL 2005, without involving triggers, is using an OUTPUT clause.

     SET NOCOUNT ON
     INSERT INTO yourTable ( ColumnA, ColumnB )
     OUTPUT inserted.yourIDColumn
     VALUES ('foo', 'bar')

     SET NOCOUNT OFF

Message was edited by: -==cfSearching==-

[BACFL]

I went with a CF solution.  What do you think about IDENITITYCOL?  It seems to be working.  But my question is whether the DB is locked and will get the correct ID or if it is possible another one could be entered before the query to get the "ID".

SELECT itemid
FROM items
WHERE itemid = #insertitemid['IDENTITYCOL']#

Thanks again everyone for the help!

[-__cfSearching__-]

SELECT itemid

FROM items

WHERE itemid = #insertitemid['IDENTITYCOL']#

I am assuming that is a typo. Because there is no reason to re-SELECT the last id when you already have it 😉

But my question is whether the DB is locked and will get the

correct ID or if it is possible another one could be entered

before the query to get the "ID".

There is no locking of the database. IIRC the result attribute of cfquery returns the correct ID by using scope_identity() internally.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

[BACFL]

Thanks everyone for the help!  All is working well now.

BCo.