CFQuery Getting GeneratedKey from Multiple Insert into SQL Server

Question

I am trying to get generated keys (or identitycol) of rows I am inserting using the multiple insert syntax.

<cfquery>
        CREATE TABLE TempPerson
            (
            PersonID INT NOT NULL IDENTITY (1,1) PRIMARY KEY,
            LastName varchar(20),
            FirstName varchar(20)
            );
</cfquery>

<cfquery result="qrResult">
    INSERT INTO TempPerson( lastName, firstName )
    VALUES( 'Smith', 'Michael' ), ('Jones','Ricky')
</cfquery>

<cfdump var="#qrResult#">

I ran this in both CF10 and Railo 4.2 in combination with both SQL Server and MySQL.

CF10 with SQL Server - no GeneratedKey returned. Only recordCount variable

RecordCount = 2

CF10 with MySQL - get the identity columns as a list, but the wrong recordCount

GeneratedKey = 1,2
RecordCount = 1

Railo 4.2 with SQL Server - gets only the last identity column

GeneratedKey = 2
RecordCount = 2

Railo 4.2 with MySQL - get identity columns as a list, and the right recordCount

GeneratedKey = 1,2
RecordCount = 2

So it looks like there is no consistency in the 4 permutations. But my most urgent issue is whether there is a way to get the generated keys from SQL Server running with CF10. Is there?

Given some of the quirks in earlier versions, I started using SQL Server's `OUTPUT` option instead of cfquery result. [Old blog entry](http://cfsearching.blogspot.com/2009/04/ms-sql-2005-more-on-underused-output.html) (no longer maintained). It is a little more work, but I have found the results more consistent than cfquery result over the various versions. If you do not find a working fix for CF10, give it a whirl and see if it works. — Leigh, Aug 07 '15 at 17:50
That probably works for SQL Server, but I was hoping to be able to use the same sql syntax and code between the two sql flavors. Maybe wishful thinking? — user2943775, Aug 07 '15 at 18:04
*Maybe wishful thinking* Unfortunately, yes. Their syntax/handling is different. — Leigh, Aug 07 '15 at 18:24

James A Mohler · Answer 1 · 2015-08-09T03:16:30.010

2

Using the sql output

<cfquery name="qrResult">
  INSERT INTO TempPerson( lastName, firstName )
  OUTPUT Inserted.PersonID
  VALUES( 'Smith', 'Michael' ), ('Jones','Ricky') 
</cfquery>

It can then be used as regular data

edited Aug 09 '15 at 03:16

answered Aug 08 '15 at 05:25

James A Mohler

11,060
15
46
72

One small correction. OUTPUT will return the data like a regular resultset. To capture it, you need to the cfquery "name" attribute, rather than "result". Side note, too bad there is not a generic equivalent that would work with MySQL as well. Closest you can get is a trigger... – Leigh Aug 08 '15 at 21:42

CFQuery Getting GeneratedKey from Multiple Insert into SQL Server

1 Answers1