Cfquery insert unexpected recordcount

Question

I need to get the recordcount of the insert into select query using cfquery.

While testing with a simple query I'm unable to get the desired result.

I'm always getting the recordcount as 1.

Code :

<cfquery name="Q_Test" datasource="DATABASE" result="queryresult">
        INSERT INTO GS_TEMP
            SELECT TRUNC (SYSDATE - ROWNUM) dt
              FROM DUAL CONNECT BY ROWNUM < 366
</cfquery>

<cfdump var="#queryresult#">

Output:

Is this expected?

Answer 1

If you want to get the number of rows then you can do:

Oracle

CREATE PROCEDURE create_dates_for_prev_year(
  out_num_rows NUMBER
)
AS
BEGIN
  INSERT INTO GS_TEMP
  SELECT TRUNC(SYSDATE) - LEVEL
  FROM DUAL
  CONNECT BY TRUNC(SYSDATE) - LEVEL >= TRUNC(SYSDATE) - INTERVAL '1' YEAR;

  out_num_rows := SQL%ROWCOUNT;
END create_dates_for_prev_year;
/

ColdFusion

<cfscript>
  sp = new StoredProc( dataSource = "DATABASE" );
  sp.setProcedure( "create_dates_for_prev_year" );
  sp.addParam( cfsqltype="CF_SQL_INTEGER", type="out", variable="numRows" );
  numRows = sp.execute.getProcOutVariables().numRows;

  writeDump( numRows );
</cfscript>

(Not tested the above code but it should be mostly correct)