Example: dbqlogV - See the Number of Rows Inserted, Updated, or Deleted for DML Statements - Teradata VantageCloud Lake

This example shows how to use the StmtDMLRowCount column to see the number of rows inserted, updated, or deleted for DML statements.

CREATE TABLE T3
  (a Integer, b VARCHAR(3), c VARCHAR(3)
);

Run a multiple-statement request, such as:

INSERT INTO T3(1, 'abc', 'def')
; INSERT INTO T3(1, 'ghi', 'jkl')
; MERGE INTO T3 AS t USING 
(SELECT 1 AS a,'stf' AS b,'xyz' AS c)  AS s on t.a = s.a
WHEN MATCHED THEN UPDATE SET c = s.c
WHEN NOT MATCHED THEN INSERT VALUES (s.a,s.b,s.c)
; MERGE INTO T3 AS t USING 
(SELECT 1 AS a,'stf' AS b,'xyz' AS c)  AS s on t.a = s.a
WHEN MATCHED THEN UPDATE SET c = s.c
WHEN NOT MATCHED THEN INSERT VALUES (s.a,s.b,s.c)
; DELETE FROM T3;

The multiple-statement request inserted 2 rows to table T3, updated 4 rows in table T3, and deleted 2 rows from table T3:

SELECT StmtDMLRowCount FROM td_metric_svc.dbqlogV;

Result:

StmtDMLRowCount 
---------------------------------------
{"Insert":2,"Update":4,"Delete":2}