Skip to main content
Ben Nadel at RIA Unleashed (Nov. 2009) with: Richard Bark
Ben Nadel at RIA Unleashed (Nov. 2009) with: Richard Bark

Getting @@RowCount After SQL INSERT INTO Statement

By
Published in Comments (26)

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.

Want to use code from this post? Check out the license.

Reader Comments

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

6 Comments

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.

15,902 Comments

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

15,902 Comments

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

6 Comments

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.

15,902 Comments

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

15,902 Comments

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

15,902 Comments

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.

11 Comments

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

15,902 Comments

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

1 Comments

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!

26 Comments

@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

15,902 Comments

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

11 Comments

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

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

That should work.

15,902 Comments

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

1 Comments

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.

15,902 Comments

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

5 Comments

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#" />

2 Comments

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.

15,902 Comments

@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)?

2 Comments

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

15,902 Comments

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

1 Comments

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.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel