Getting the primary key of the records I just inserted

Question

This is becoming kind of tricky. I want to output the primary key of the record I just inserted. I am doing an insert of multiple records:

My insert statement looks something like this:

<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
    <cfoutput query="myFileList">
        INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
    </cfoutput>
</cfquery>

<cfset newID = result.IdentityCol>
<cfoutput>#newID#</cfoutput>

And this throws a CF Error:

"Element IDENTITYCOL is undefined in RESULT."

So I'm thinking that there is hopefully another way to get PK of the record I just inserted. Any thoughts?

Here is the code I used according to the example:

<cftransaction>
    <cfquery name="importFiles" datasource="#dsn#" result="result">
        <cfoutput query="myFileList">
        INSERT INTO tbl_logfiles (originalFile, originalFileSize) VALUES ('#name#', '#length#');
        </cfoutput>
    </cfquery>

    <cfquery name="getID" datasource="#dsn#">
         select Max(fileID) as NewID from tbl_logfiles;
    </cfquery>
</cftransaction>

<cfset newID = getID.NewID>
<cfoutput> #newID# </cfoutput>

The output I get from #newID# is 280, which is the highest fileID in my database table at this time. Weird.

What I am trying to get is the last whatever N records I imported. I was hoping there was a way I could output it somehow from the cfoutput tag based on the cfquery.result.

score 4 · Answer 1 · edited May 23 '17 at 11:44

I know this is an old thread, but ... never use SELECT MAX(Column) to obtain the last ID inserted. It is not thread safe, even within a transaction (set at the default level).

Since the result attribute only returns a single identity value, you must use separate queries instead (for SQL Server 2000 anyway). Then you can grab and store the new id after each insert.

Also, be sure to use cfqueryparam on all parameters. In addition to protecting against sql injection, it will boost performance of the insert queries by encouraging the database to reuse the same query plan.

<cfset newIDArray = []>
<cftransaction>
    <cfloop query="myFileList">
        <cfquery result="result" ...>
           INSERT INTO myTablename (...) VALUES (...);
        </cfquery>
        <!--- For CF9+ use generic result.generatedKey instead --->
        <cfset arrayAppend(newIDArray, result.identityCol)>
    </cfloop>
</cftransaction>

Note: As Nicholas mentioned in the comments, CF9+ introduced a new database agnostic key name result.generatedKey. So if you are using a later version, that key name is preferred over result.identityCol for greater flexibility.

Obviously there are better options like OUTPUT for SQL Server 2005.

Excellent answer, but to bring it up to date newer versions of ColdFusion (I believe 9+) support a database agnostic value: generatedKey. So result.identityCol can be changed to result.generatedKey. — Nicholas, Apr 01 '15 at 14:13

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

It would be MSSQL 2000 or 2005. If you're using 2005, you can use the OUTPUT clause in your INSERT query to keep track of the PK values created by the insert.

See this BOL article for more on OUTPUT.

To put this in your solution, you may have to set it up as a stored procedure and call the SP from CF.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 26 '11 at 23:38

Phil Helmer

1,230
8
8

`OUTPUT` is only available on 2005 and 2008, and the OP said he's on 2003, which probably means he's really on 2000 :) – Dan Short Apr 27 '11 at 16:16
aah yes, I have confirmed I am using MSSQL 2000 (running Windows Server 2003). – Robbiegod Apr 27 '11 at 19:49
Sorry, I forgot to mention the part about it also needing 2005. – Phil Helmer Apr 29 '11 at 03:27

M.R. · Accepted Answer · 2011-04-27T21:35:33.600

0

There are various ways to do it in coldfusion - check it out here: http://tutorial480.easycfm.com/

EDIT:

Try this method, it will definitly work:


<cftransaction>
<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
<cfoutput query="myFileList">
INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
</cfoutput>
</cfquery>
<cfquery name="getID" datasource="#mydsn#" result="#result#">
select Max(id) as NewID from myTablename;
</cfquery>
</cftransaction>
<cfset newID = getID.NewID&gt
<cfoutput>
#newID#
</cfoutput>

EDIT based on comment:

Yes, for sure. You have a cfoutput query within the cfquery tag, so you are inserting row after row, and you are creating a new ID everytime. If you need the IDs for each one, then you need to wrap the whole thing in your cfoutput:


<cftransaction>
<cfoutput query="myFileList">
<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
</cfquery>
<cfquery name="getID" datasource="#mydsn#" result="#result#">
select Max(id) as NewID from myTablename;
</cfquery>
<cfset newID = getID.NewID&gt
#newID#
</cfoutput>
</cftransaction>

edited Apr 27 '11 at 21:35

answered Apr 27 '11 at 05:03

M.R.

4,737
3
37
81

Thanks MR for the link. I had already found that and tried that in my script, but it kept failing. The solutions on those pages are for different database types. I tried the MSSQL solution and it doesnt work, perhaps because of the version i am on. – Robbiegod Apr 27 '11 at 19:53
Which one did you try? I've tried all of them at one point or another, and they all worked (in sql server 2000) – M.R. Apr 27 '11 at 19:56
I believe i see what the problem is...When i do a , it acts like 1 record was inserted because I have multiple records being inserted in one cfquery tag. So, in this case, it selecting the MAX ID number, which would be the last record in the database. But i am actually importing 8 records in my exmaples, but i think it only counts as one because the multiple insert into lines in the cfquery. – Robbiegod Apr 27 '11 at 21:25
wow, cool, yep you have been incredibly helpful! Yes, that last edit did the trick. – Robbiegod Apr 27 '11 at 21:45
Sorry but this is incorrect. SELECT MAX(..) is **not thread safe**. Under heavy load the code above may return the wrong ID values. – Leigh Apr 02 '15 at 16:05

score 0 · Answer 4 · answered Apr 27 '11 at 16:15

I think in this case you'd need to do each of the inserts in their own cfquery tag. If you were doing one insert at a time, that wouldn't be a problem, but because you're doing multiple inserts (and you're on an older version of SQL Server), there's no way to do this with a single cfquery. If you're on 2005 or 2008 you could potentially use the OUTPUT method mentioned in another reply.

Getting the primary key of the records I just inserted

4 Answers4

Linked