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

Question

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!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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?