Getting @@RowCount After SQL INSERT INTO Statement
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.
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>
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.
@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!
@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.
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.
@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.
I use:
SELECT IDENT_CURRENT('TableName') as NewID
@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.
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.
@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.
@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.
This help.
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!
@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
@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?
<CFQUERY....>
declare @myvar int
//do calculations on @myvar
SELECT @myvar as result
</CFQUERY>
<cfoutput>#queryname.result#</cfoutput>
That should work.
i want the total of all the row count to be represented in 1 row count. Is this possible in MS sql ?
thanks
@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.
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.
@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.
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#" />
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.
@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)?
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...
@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.
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.