Getting @@RowCount After SQL INSERT INTO Statement

Posted November 28, 2007 at 2:57 PM by Ben Nadel

Tags: SQL

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.

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment