Re: Extract new rows only from a table with SQL to SQL server

Can you really not add a column, even if it is not disruptive to the SQL application designed to use the table? If the table is used for any native access, the following solution would not work.

I suggest looking into adding a ROW CHANGE TIMESTAMP column that is IMPLICITLY HIDDEN.

This column would be assigned a value whenever an insert or update of the row occurs.

It has the benefit that it is only visible to SQL when you mention it in an SQL statement. It is not included when you SELECT * from the table. INSERT does not expect a value for it unless you explicitly indicate you are inserting into it.

Sue Romano
Db2 for IBM i development

The current process running has an SQL server using ODBC running a query against a IBM i table with 6 million rows (table scan, no selection criteria) and updating a SQL server table every night.

Right now, the process is a refresh of the 6 million rows on SQL server. In reality we only need the newest rows since the last run. Ordinarily I would just use a timestamp on the table to pull rows since a >specific time, but this table does not have a timestamp on it (JDE supplied table so I cannot modify it)

Is there a way we can use SQL to get only the newest rows on the table? (Triggers are not available for the same reason I can?t modify the table)

My guess is no, without a timestamp we cannot, but hopefully someone will have an enlightened answer.

Might temporal tables play in this scenario?

Sue Romano
Db2 for IBM i development