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?

Answer 1

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