Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with:

Getting @@RowCount After SQL INSERT INTO Statement

By Ben Nadel on
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:

  • <!--- 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:


 
 
 

 
Getting @@RowCount After SQL INSERT INTO Statement Has Executed  
 
 
 

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.



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:
<!--- use the result attribute for cfquery --->
<cfquery name="qry" datasource="#myDSN" result="qryResult">
INSERT INTO myTable (myField)
SELECT yourField
FROM yourTable
WHERE ID < 5
SELECT @@rowcount as numOfRecs
</cfquery>

<cfdump va="#qryResult#" />

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>

Reply to this Comment

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.

Reply to this Comment

@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!

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

Anyone know if there is a way to reference locally created SQL variables within the CFQUERY outside of the CFQUERY, other than the global SQL variables @@identity etc? I want to have SQL do my numebr crunching to save the pathetic CFSCRIPT compiler some time and can't find a way to use my output my SQL results variables.

Example:

<CFQUERY....>
declare @myvar int
etc.
</CFQUERY>

<CFOUTPUT>#@myvar#</CFOUTPUT>

doesn't work... :(

thx, great site!

Reply to this Comment

@Scott

that can not work. The SQL statement is not processed by CF, but, by the database. CF can only reference what the database sends back. Local variables are only sent back if you use a corresponding return statement. Classic case for a stored proc with IN and OUT variables. ;-)

Cheers

Chris

Reply to this Comment

@Scott,

You'd have to select the calculated values as column values in your return query. However, it sounds like you are operating under the assumption that there is a performance issue with the ColdFusion compiler? Would you care to expand on that more?

Reply to this Comment

<CFQUERY....>
declare @myvar int
//do calculations on @myvar
SELECT @myvar as result
</CFQUERY>

<cfoutput>#queryname.result#</cfoutput>

That should work.

Reply to this Comment

i want the total of all the row count to be represented in 1 row count. Is this possible in MS sql ?

thanks

Reply to this Comment

@William,

You could run a SUM() on the records after you insert them; but, I don't know of a way to get it without re-running a query.

Reply to this Comment

Totally fool proof, this one:

after your insert just write this:
SET @ID = SELECT ID FROM inserted

INSERTED is new for sql 2005, and can be used for all inserted columns, not only id.

Reply to this Comment

@Jos,

Very interesting. I have been on MySQL lately, so I have not seen much of the innovations used in MS SQL Server. I wonder why they didn't give it some @@ variable name (ex. @@Inserted). Either way, thanks for the tip.

Reply to this Comment

Christoph's suggesing did not work for me. I had to use SET NO COUNT ON:

<cfquery name="qry" datasource="#myDSN">
SET NOCOUNT ON

INSERT INTO myTable (myField)
SELECT yourField
FROM yourTable
WHERE ID < 5

SET NOCOUNT OFF

SELECT @@rowcount as numOfRecs
</cfquery>

<cfdump var="#qry#" />

Reply to this Comment

Hi,
I'm trying to figure out how to insert the row count as a value.

For example:

insert into TempImport (Code, OldName, Description, Def, NewName)
Select '2', Name, Description, 'Department', (@@RowCount)
from LLEntry where LLEntryID > '0' and LLDefID = '4'

I'm trying to use the RowCount for the NewName value. But for every row it comes back with a value of 1.

Reply to this Comment

@Dave,

I am not sure I understand what you are asking the query to do? Your SELECT might return multiple records; do you want the @@RowCount to reflect the total number of selected record (ie. be the same for every row, after the fact)?

Reply to this Comment

Hi Ben,

The output would be like this:

Row 1: 2, OldName, Desc., Dept., 1
Row 2: 2, OldName, Desc., Dept., 2
Row 3: 2, OldName, Desc., Dept., 3
Row 4: 2, OldName, Desc., Dept., 4

So the Sql statement will process the records from LLEntry table. And each time it processes a record, I want it to increment the count and then place that value in the last column.

Currently, what it's doing is:
Row 1: 2, OldName, Desc., Dept., 1
Row 2: 2, OldName, Desc., Dept., 1
Row 3: 2, OldName, Desc., Dept., 1
Row 4: 2, OldName, Desc., Dept., 1

I tried using CASE.
I tried declaring a variable.

Thanks...

Reply to this Comment

@Dave,

Ah, I see; basically, you're trying to get the row "number" as part of the record set itself. Are you using this to then perform some sort of pagination? I think one way I've tried to deal with this is taking the result of that query and altering it, adding an IDENTITY column. The identity column will act like a row number.

Of course, that only works with some databases. And, I might be way off - it's been a while since I've used a database that allows derived tables to be stored in a variable.

Reply to this Comment

Looks like this is an old topic.
But to add to it:

<cfquery .... result="qryResult">
...SQL LOGIC
</cfquery>

qryResult.IDENTITYCOL should contain new ID of an inserted row

qryResult.RecordCount (for an update or insert I assume) will give you the number of rows affected.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.