I just made a happy little discovery about SQL's INSERT INTO statement. After you run the INSERT INTO statement on a temporary data table, the SQL variable, @@RowCount, contains the number of records that were inserted into the temp table. Take a look at this demo:
<!--- Create, populate, and query the temp ID table. ---> <cfquery name="qID" datasource="#REQUEST.DSN.Source#"> DECLARE @valid TABLE ( id INT, row_id INT IDENTITY( 1, 1 ) ); <!--- Populate the valid ID table using some UNIONed SELECT statements. This will manually populate the ID column and the let the row_id auto-populate the row index values. ---> INSERT INTO @valid ( id )( SELECT 2 UNION ALL SELECT 4 UNION ALL SELECT 6 UNION ALL SELECT 8 UNION ALL SELECT 10 ); <!--- Select the number of records that were affected by the last SQL statement. ---> SELECT v.id, v.row_id, ( @@RowCount ) AS row_count FROM @valid v </cfquery> <!--- Output the ID query. ---> <cfdump var="#qID#" label="Test For @@RowCount" />
Here, we create a temporary table, @valid, and then populate it with 5 values using some UNION'd SELECT statements. This is just a simulation; ordinarily, my INSERT INTO statement would house a SELECT statement that pulls data from an actual database table. Either way, the result is the same, and is that after the INSERT INTO statement executes, the local variable @@RowCount holds the number of records that were inserted into the temporary table.
To prove this, we are selecting that value, @@RowCount, as one of the computed columns in the final SELECT statement. Running the above code, we get the following CFDump output:
Notice that the row_count column of the resultant query contains all 5s; this is the number of records we inserted into the temporary table and is equal to the number of records returned.
This might seem like a really insignificant entry, but if you look at my previous post on SQL-server-side pagination, you will notice that I used to run a COUNT() aggregate on my temp data table to get the inserted row count. Talk about superfluous processing! I'm not doing that anymore.
Want to use code from this post? Check out the license.