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 »
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
Comments (11) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
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>
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