17.10 - Example: Using StmtDMLRowCount - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Dictionary

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1092-171K
Language
English (United States)

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}