You can enable the NumRowsAffected output port to return the number of rows affected by the INSERT, UPDATE, or DELETE query statements in each input row. The Integration Service returns the NumRowsAffected for each statement in the query. NumRowsAffected is disabled by default.
When you enable NumRowsAffected in query mode, and the SQL query does not contain an INSERT, UPDATE, or DELETE statement, NumRowsAffected is zero in each output row.
When you enable NumRowsAffected and the transformation is configured to run in script mode, NumRowsAffected is always NULL.
The following table lists the output rows the SQL transformation generates when you enable NumRowsAffected in query mode:
UPDATE, INSERT, DELETE only |
One row for each statement with the NumRowsAffected for the statement. |
One or more SELECT statements |
Total number of database rows retrieved. NumRowsAffected is zero in each row. |
DDL queries such as CREATE, DROP, TRUNCATE |
One row with zero NumRowsAffected. |
When the SQL transformation runs in query mode and a query contains multiple statements, the Integration Service returns the NumRowsAffected for each statement. NumRowsAffected contains the sum of the rows affected by each INSERT, UPDATE, and DELETE statement in 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 Integration Service returns one row from the DELETE statement. NumRowsAffected is equal to one. It returns one row from the SELECT statement, NumRowsAffected is zero. It returns one row from the INSERT statement with NumRows Affected equal to one.
The NumRowsAffected port returns zero when all of the following conditions are true:
The database is Informix.
The transformation is running in query mode.
The query contains no parameters.
ASK INFAPreview
