Skip to main content
N
notbug
New Participant
August 30, 2007
Answered

Getting number of inserted records

  • August 30, 2007
  • 8 replies
  • 1060 views
Hi,

I'm moving from 6.1 to 8.0 and have found a query which works in 6.1 but not in 8.0, and I'm just wondering if there is a workaround for it.

What I'm doing is inserting multiple records into a table and using @@rowcount to find out the number of records inserted, this code worked in 6.1 but in 8.0 the "qry" variable is not defined. Any help would be appreciated, thanks.

This topic has been closed for replies.
Correct answer cf_dev2
You must use SET NOCOUNT to supress the record count returned by the insert

SET NOCOUNT ON
INSERT INTO emp
SELECT f_name, l_name
FROM applicants
WHERE hired = 1

SELECT @@rowcount AS NumberOfRecords
SET NOCOUNT OFF

8 replies

C
cf_dev2
Inspiring
August 31, 2007
PaulH wrote:
> RECORDCOUNT how many rows effected by this statement, seems to work
> for UDPATE,INSERT, etc. very sweet.

I knew about @@IDENTITY but not recordcount. Nice!
N
Newsgroup_User
Inspiring
August 31, 2007
bubbabouy wrote:
> <cfquery name="qry">
> INSERT INTO emp
> SELECT f_name, l_name
> FROM applicants
> WHERE hired = 1
> </cfquery>

ah i'm so dumb. if you're on cf8, you can simply add the result argument to your
cfquery

<cfquery name="qry" result="myResult">

then you can access a bunch of interesting stuff like:

CACHED this query cached or not
EXECUTIONTIME how long
IDENTITYCOL identity value (or maybe a list) for this query
RECORDCOUNT how many rows effected by this statement, seems to work for
UDPATE,INSERT, etc. very sweet.
SQL sql for this query


N
Newsgroup_User
Inspiring
August 31, 2007
cf_dev2 wrote:
> It works just fine for me with 6.1 using the builtin sql server driver (not MS). I don't know which version though.

w/out quoting not sure what you're replying to in my news reader, but are you
saying you *don't* need a NOCOUNT ON/OFF block w/the 6.1 DD driver? it's
certainly been a while but i recall that you *always* needed a NOCOUNT when
using multiple SQL statements, even as far as back as cf3/4/5 & their ODBC
drivers. otherwise cf would interpret the rowcount returned by the 1st SQL
statement as a returned result set & stop looking for any more data.

C
cf_dev2
Inspiring
August 31, 2007
PaulH wrote:
> are you saying you *don't* need a NOCOUNT ON/OFF block w/the 6.1 DD driver?

Yes. I even tried setting NOCOUNT OFF before the insert. Perhaps it has to do with the DD driver version? I don't know which version is installed (3.3 maybe?) just that they're not the ones that shipped with 6.1. Any other settings you can think to check?

> certainly been a while but i recall that you *always* needed a NOCOUNT when
> using multiple SQL statements, even as far as back as cf3/4/5 & their ODBC

Maybe my memory is shot, but I thought that it worked without NOCOUNT in *some* versions, but did not work in others. I understand that it shouldn't work .. but it does on this machine.

C
cf_dev2
Inspiring
August 31, 2007
It works just fine for me with 6.1 using the builtin sql server driver (not MS). I don't know which version though.
N
Newsgroup_User
Inspiring
August 31, 2007
bubbabouy wrote:
> This query works in 6.1 without having to set NOCOUNT. In any case adding
> the line SET NOCOUNT OFF does not help, the "qry" variable is still not
> defined if using 8.0.

no, w/out the NOCOUNT OFF that wouldn't work in any version of cf using the
builtin JDBC driver.
C
cf_dev2
Inspiring
August 30, 2007
I know it works in 6.1, but it shouldn't. You just got lucky.

Is the first line SET NOCOUNT ON? I don't have access to CF8 right now so I can't test it.
N
notbugAuthor
New Participant
August 30, 2007
Yes sorry I missed the first line. Setting NOCOUNT ON worked. Thanks very much for your help.
N
notbugAuthor
New Participant
August 30, 2007
This query works in 6.1 without having to set NOCOUNT. In any case adding the line SET NOCOUNT OFF does not help, the "qry" variable is still not defined if using 8.0.
C
cf_dev2Correct answer
Inspiring
August 30, 2007
You must use SET NOCOUNT to supress the record count returned by the insert

SET NOCOUNT ON
INSERT INTO emp
SELECT f_name, l_name
FROM applicants
WHERE hired = 1

SELECT @@rowcount AS NumberOfRecords
SET NOCOUNT OFF
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices