Getting @@RowCount After SQL INSERT INTO Statement : Kinky Solutions : A Student's Perspective by Ben Nadel

Getting @@RowCount After SQL INSERT INTO Statement

Posted November 28, 2007 at 2:57 PM

Tags: SQL

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:

Launch code in new window » Download code as text file »

<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>
<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.

Download Code Snippet ZIP File

Grouping JOIN Clauses In SQL

Using CFPOP And CFMAIL For Bidirectional SMS Text Messaging In ColdFusion

Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

Hey Ben,

great post! You are talking about MS SQL Server, correct? For MSSQL, I have two additions...

you can select @@rowcount with physical tables as well, if you use cfquery's result attribute:

<cfquery name="qry" datasource="#myDSN" result="qryResult">
INSERT INTO myTable (myField)
SELECT yourField
FROM yourTable
WHERE ID < 5
SELECT @@rowcount as numOfRecs
</cfquery>

Plus, you can select the last inserted identity value, as long as you privide a name to an insert query.
<cfquery name="qry" datasource="#myDSN#">
SET NOCOUNT ON
INSERT INTO myTable (myField)
VALUES ('abc')
SELECT @@identity AS newID
SET NOCOUNT OFF
</cfquery>

<cfoutput>
new ID: #qry.newID#
</cfoutput>

Posted by Christoph Schmitz on Nov 28, 2007 at 4:45 PM

Christoph:

Just a note, but you should really use SCOPE_IDENTITY() rather than @@IDENTITY, because you can run into conditions where you get an ID you're not expecting otherwise.

Posted by Jason Rushton on Nov 28, 2007 at 5:02 PM

@Christoph,

Yes, I am talking about MS SQL Server. I assume that all the other databases have something similar, but this is the only one that I use.

And yeah, @@Identity might just be the coolest thing since sliced bread!

Posted by Ben Nadel on Nov 28, 2007 at 5:06 PM

@Jason,

Can you shed any light on those scenarios? I have been using straight up @@Identity, and I certainly don't want to be messing up any ID references.

Posted by Ben Nadel on Nov 28, 2007 at 5:12 PM

Triggers are the big thing that can give you problems.
If your insert initiated a trigger which did it's own insert, using @@IDENTITY would give you the identity for the triggers insert. Scope_Identity() would give you the identity you were probably expecting.

Posted by Jason Rushton on Nov 28, 2007 at 5:39 PM

@Jason,

Ahhh, that makes sense. I don't do much in the way of triggers, so at least I don't have a lot of bad code out there (now). However, my code certainly isn't future-proofed against someone who might come in and add trigger... make me a little nervous. I will try to get in the habit of using the scoped identity. Thanks for the tip.

Posted by Ben Nadel on Nov 28, 2007 at 5:42 PM

I use:

SELECT IDENT_CURRENT('TableName') as NewID

Posted by Matt Voorman on Nov 28, 2007 at 9:49 PM

@Matt,

I have never heard of that one. Looking at the documentation, it seems that that is new to SQL Server 2005 (but I have not tested it). Thanks for the tip.

Posted by Ben Nadel on Nov 29, 2007 at 7:07 AM

Out of the SQL Server documentation:

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

* IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

* @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

* SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Found on: http://msdn2.microsoft.com/en-us/library/ms175098.aspx

It looks like as long as you refer to the Identity right after the query that creates it, IDENT_CURRENT() and SCOPE_IDENTITY() do the same thing. The flexibility of IDENT_CURRENT() is that you can put it just about anywhere.

Posted by Ben Nadel on Nov 29, 2007 at 7:09 AM

@Ben, That's not quite right. IDENT_CURRENT is going to be subject to returning the wrong information if there are multiple simultaneous inserts on the table.

I believe that if it's in an explicit transaction, IDENT_CURRENT will be the same as @@IDENTITY in all situations, but I feel a lot more comfortable with @@IDENTITY or SCOPE_IDENTITY than I do with IDENT_CURRENT.

Also, it's probably obvious, but I've seen a lot of developers write:

SELECT @@IDENTITY FROM tablename

Don't do this-- you're getting back one copy of @@IDENTITY for each row in tablename. SELECT @@IDENTITY, no tablename needed, it automatically references the last insert in your session.

Posted by David C-L on Nov 29, 2007 at 6:04 PM

@David,

Yeah, I use to do weird stuff like that. Then I learned the power of selecting variables directly:

SELECT
@@Identity AS id
;

Sooo much more efficient.

Posted by Ben Nadel on Nov 29, 2007 at 6:06 PM

Post Comment | Ask Ben

Getting @@RowCount After SQL INSERT INTO Statement : Kinky Solutions : A Student's Perspective by Ben Nadel