Synchronize statement

The synchronize statement works indeed very well with insert, removing the technical identity column rowid$ from the insert logic.

But unless if I’m wrong I’m having difficulties with synchronizing from an @inmemorystorage table into an @sql container with an insert or update clause.

Invantive Query Tool reports the error:

One or more errors occurred.
Impossible to update identity column ‘rowid$’.

I have also described my subject here.

I am asking myself may be there is something wrong in Query Tool logic on a:

to          account_invoice@inmemorystorage
from        account_invoice@sage_sql2019_vega_replication
with        insert or update
by          id

in regards with the rowid$, Query Tool trying to update identity column whereas it should not.

The question is not completely clear yet on how to reproduce the problem, but maybe you are referring to the need to exclude the column rowid$ with identity-generated values from the update-statement fired by the Invantive synchronize-statement.

This can be done by adding except to the update-clause as in for instance:

synchronize target@targetdc
from        source@inmemorystorage
with        insert
            update all except created_by, created_at, date_created, session_created, uid
            ignore changes to modified_by, modified_at, date_modified, session_modified, created_by, created_at, date_created, session_created, uid
by          column1
,           column2

In this sample, the update does not consider a row changed when only changes are found in the listed columns:

  • modified_by
  • modified_at
  • date_modified
  • session_modified
  • created_by
  • created_at
  • date_created
  • session_created
  • uid

It also only tries to update the row for columns not in the list of:

  • created_by
  • created_at
  • date_created
  • session_created
  • uid

Effectively this can be used to provide both create and modification audit-information on initial insert, and only modification audit-information on subsequent updates.

The uid column is some kind of unique value, so might resemble your need with rowid$.

Sorry for not being clear at that time of this ticket.

In fact the issue is solved by using the except rowid$ in the statement.

At that time it was the first time I was using this kind of workflow:

  • using Invantive to execute and store some values in a @inmemorystorage table, thus a rowid$ column is automatically added by Invantive
  • then use the synchronize with insert statement to update a @sql table; itself also containing a rowid$ column created by Invantive SQL.

So when

  • Source table has not been created by Invantive SQL, synchronize does not require to add the except rowid$,
  • Source table has been created by Invantive SQL, synchronize requires to add the except rowid$.

Yes, that seems like a great approach!