Insert into SELECT ...., @@rowcount = ?

• touchmeknot

  SSCommitted

  Points: 1769

  More actions

  July 24, 2012 at 3:33 pm

  #278475

  Hello everyone,

  I am running a statement:

  INSERT INTO LinkedServer.DBName.dbo.TableName (col1, col2)

  SELECT Col1, Col2

  FROM dbo.TableName

  SELECT @@ROWCOUNT

  I want to check the count in both the above tables. May I know, what would @@ROWCOUNT above return ? The number of records selected or the number of records inserted ? Also, if you can tell me, how can it be verified, it would be very helpful.

  Thanks in advance!

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  July 24, 2012 at 3:43 pm

  #1517097

  It's a bit of a circular argument, but per BOL, select @@ROWCOUNT returns the number of rows affected by the previous statement. The last statement is an INSERT so that's what @@rowcount is returning (the # of rows being inserted).

  That said - since you're inserting what you're selecting, the number is the same either way.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• touchmeknot

  SSCommitted

  Points: 1769

  More actions

  July 24, 2012 at 4:09 pm

  #1517106

  Thanks for the reply Matt.

  I was testing about, how can I verify the same and I tried -

  INSERT INTO ....

  SELECT 10/0, 1

  SELECT @@ROWCOUNT

  As we know, 10/0 , divide by zero and it wouldn't insert any record. So the number of records inserted would be 0. Moreover, another point here is that- there is linked server involved. Not sure if that would make a difference.

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  July 25, 2012 at 8:45 am

  #1517400

  Well if you're concerned that the numbers will NOT be the same (or you want to verify), then insert it into a temporary table of some sort, and verify that the counts are the same in both.

  That said - you should get an error if the insert doesn't happen in full. with a linked server you might get a timeout etc, but you should see some kind of feedback that it didn't complete successfully.

  You can ALSO perform the insert within a transaction, then check the rowcounts before committing.

  I can only talk in generalities right now, since you're not providing much in the way of specifics.

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?