Developer Transformation Guide

10.4.0
- 10.5.2
- 10.5
- 10.4.1

Back Next

Number of Rows Affected

Enable the NumRowsAffected output port to return the number of rows that the INSERT, UPDATE, or DELETE query statements change for each input row. You can configure the NumRowsAffected output port for SQL queries.

The Data Integration Service returns the NumRowsAffected for each statement in the query. NumRowsAffected is disabled by default.

When you enable NumRowsAffected and the SQL query does not contain an INSERT, UPDATE, or DELETE statement, NumRowsAffected is zero in each output row.

When the SQL query contains multiple statements, the Data Integration Service returns the NumRowsAffected for each statement. NumRowsAffected contains the sum of the rows that the INSERT, UPDATE, and DELETE statements change for an input row.

For example, a query contains the following statements:

DELETE from Employees WHERE Employee_ID = ‘101’;
SELECT Employee_ID, LastName from Employees WHERE Employee_ID = ‘103’;
INSERT into Employees (Employee_ID, LastName, Address)VALUES (‘102’, 'Gein', '38 Beach Rd')

The DELETE statement affects one row. The SELECT statement does not affect any row. The INSERT statement affects one row.

The Data Integration Service returns one row from the DELETE statement. NumRowsAffected is equal to one. The Data Integration Service returns one row from the SELECT statement, NumRowsAffected is zero. The Data Integration Service returns one row from the INSERT statement with NumRowsAffected equal to one.

Rename Saved Search

Table of Contents

Developer Transformation Guide

Developer Transformation Guide

Number of Rows Affected

Number of Rows Affected