如何获取事务插入的行数 [英] How to get number of rows inserted by a transaction

问题描述

我必须管理一个日志，我必须在其中查看事务插入的行数.有没有办法动态地做到这一点?

I have to manage a log where i have to see the number of rows that are inserted by a transaction. Is there any way of doing it dynamically ?

推荐答案

@@ROWCOUNT 会给出最后条SQL语句影响的行数，最好捕捉到局部变量中遵循相关命令，因为它的值将在您下次查看时发生变化:

@@ROWCOUNT will give the number of rows affected by the last SQL statement, it is best to capture it into a local variable following the command in question, as its value will change the next time you look at it:

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 行

you get Rows value of 2, the number of inserted rows, but ROWCOUNT is 1 because the SELECT @Rows=@@ROWCOUNT command affected 1 row

如果您的事务中有多个 INSERT 或 UPDATE 等，您需要确定您希望如何计算"正在发生的事情.您可以为每个表设置单独的总数、单个总计值或完全不同的值.您需要为要跟踪的每个总数声明一个变量，并在适用于它的每个操作之后添加到该变量中:

if you have multiple INSERTs or UPDATEs, etc. in your transaction, you need to determine how you would like to "count" what is going on. You could have a separate total for each table, a single grand total value, or something completely different. You'll need to DECLARE a variable for each total you want to track and add to it following each operation that applies to it:

--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

这篇关于如何获取事务插入的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！