SQLCMD returns incorrect "rows affected"-count when inserting more than 2.147.483.648 rows

Question

We noticed that when more than 2.147.483.648 rows are inserted using SQLCMD to execute an insert-query (using a "SELECT" on a source-table with 8.6 billion rows), that then the "xxx rows affected" message reports an incorrect number of rows.

In our example, we get following counts:

• Query used:
  insert into dbo.[TargetTable] with (tablock) (TargetID) ( select SourceID from dbo.[SourceTable] --> this table contains ``8.673.449.751 rows )
print CONCAT(N' ',ROWCOUNT_BIG(), N' rows inserted. ');
• Result:
  (83515159 rows affected) 8673449751 rows inserted.

The ROWCOUNT_BIG()-value is correct, the "rows affected"-number is not.

We see that it has to do with the max-value of the "int" data-type (max-value int = 2147483647) as we can get to the bold number as follows:

83515159 = 8.673.449.751 - (4 x (2147483647 + 1)

What is also curious is that in SSMS 18.12.1, this incorrect "rows affected"-number is simply displayed in the Messages-output, but in SSMS 19.3, the "rows affected"-message is omitted as soon as the value exceeds "2147483647 rows affected".

This clearly is a bug and as I couldn't find any info about this on the www, I wanted to report it through this channel.

Please advise if I can/should report this elsewhere

Best regards,

Charles

Answer 1

Hi @Charles Fierens

This clearly is a bug and as I couldn't find any info about this on the www, I wanted to report it through this channel.

For this bug issue, you can post it on the feedback site. Also, you could upvote the similar post so that the Microsoft team will can see it more easily.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

There is a new SQLCMD, implemented in Go. I tested it with three milliard rows and I got back:

(3000000000 rows affected)

Look further here: https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16&tabs=go%2Cwindows&pivots=cs1-bash