server sql 数据总行数_SQL Server-如何获取事务插入的行数

@@ ROWCOUNT将给出受最后一条SQL语句影响的行数，最好在有问题的命令后将其捕获到局部变量中，因为下次您查看它时，其值将更改：

DECLARE @Rows int

DECLARE @TestTable table (col1 int, col2 int)

INSERT INTO @TestTable (col1, col2) select 1,2 union select 3,4

SELECT @Rows=@@ROWCOUNT

SELECT @Rows AS Rows,@@ROWCOUNT AS [ROWCOUNT]

输出：

(2 row(s) affected)

Rows ROWCOUNT

----------- -----------

2 1

(1 row(s) affected)

您得到Rows的值为2，即插入的行数，但是ROWCOUNT为1，因为SELECT @Rows=@@ROWCOUNT命令影响了1行

如果您的事务中有多个INSERT或UPDATE等，则需要确定如何“计算”正在发生的事情。 您可以为每个表分别设置总计，单个总计值或完全不同的值。 您需要为每个要跟踪的总计选择一个变量，并在对其应用以下每个操作之后将其添加到该变量：

--note there is no error handling here, as this is a simple example

DECLARE @AppleTotal int

DECLARE @PeachTotal int

SELECT @AppleTotal=0,@PeachTotal=0

BEGIN TRANSACTION

INSERT INTO Apple (col1, col2) Select col1,col2 from xyz where ...

SET @AppleTotal=@AppleTotal+@@ROWCOUNT

INSERT INTO Apple (col1, col2) Select col1,col2 from abc where ...

SET @AppleTotal=@AppleTotal+@@ROWCOUNT

INSERT INTO Peach (col1, col2) Select col1,col2 from xyz where ...

SET @PeachTotal=@PeachTotal+@@ROWCOUNT

INSERT INTO Peach (col1, col2) Select col1,col2 from abc where ...

SET @PeachTotal=@PeachTotal+@@ROWCOUNT

COMMIT

SELECT @AppleTotal AS AppleTotal, @PeachTotal AS PeachTotal