How to find number of rows affected by INSERT INTO statement in ColdFusion And MySql Database?

Question

I have following query in ColdFusion:

<cfquery name="test" datasource="test">
    INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
</cfquery> 

I want to find number of affected rows count. Is there any way to find this in ColdFusion?

Answer 1

<cftransaction>
    <cfquery name="test" datasource="test">
        INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
    </cfquery>
    <cfquery name="test1" datasource="test">
        SELECT ROW_COUNT() AS numberOfRowsAffected
    </cfquery>
</cftransaction>
<cfdump var="#test1.numberOfRowsAffected#">

Or

You can make changes in the settings to allow multiple sql statements withing one cfquery. To make that happen, make changes as mentioned below:

In ColdFusion Admin, go to your Data Source definition form and add allowMultiQueries=true to the Connection String box. Once you do that, you can pass multiple semi-colon separated queries in a single CFQUERY tag. Just make sure you're using CFQUERYPARAM to screen for SQL Injection Attacks.

Answer 2

Try this one.

<cfquery datasource="test" result="myResult">
  INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
</cfquery>
<cfset getNumberOfRecords = listLen(#myResult.generated_key#)>
<cfdump var="#getNumberOfRecords#">

myResult.generated_key contains the list of generated ids, so we can find how many rows have been inserted when we use listLen() function.

Answer 3

Use the result attribute and then access the recordCount key of the result.

<cfquery result="myResult" datasource="test">
  INSERT INTO test (id,name) VALUES (1,"santy"),(2,"raj"),(3,"nil")
</cfquery> 

<cfdump var="#myResult.recordCount#">