17.00 - Example: Using StmtDMLRowCount - Teradata Database

Teradata Vantageā„¢ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

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

Run a multistatement 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 multistatement request inserted 2 rows to table T3, updated 4 rows in table T3, and deleted 2 rows from table T3:

SELECT StmtDMLRowCount FROM DBC.QryLogV;

Result:

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