Thread: Manage SCD 2 table using the INSERT --- ON CONFLICT
Hello,
I am trying to use the INSERT ON CONFLICT syntax to build an SCD 2 table loader.
here are the behaviors I need to achieve:
SCD 2 table: TAB_OUT
new candidate records: TAB_IN
The table TAB_IN contains the new candidate records to be submitted to TAB_OUT.
Case 1: The record presented by TAB_IN is already present in TAB_OUT and the fields were not modified
-> DO NOTHING
Case 2: The record presented by TAB_IN is not present in TAB_OUT
-> INSERT NEW RECORD (OK)
Case 3: The record presented by TAB_IN is already presente in TAB_OUT and the fields were modified
-> CLOSE existing record in TAB_OUT(OK)
-> INSERT new RECORD (KO)
I can't find a way to retrieve the list of records that were updated ON CONFLICT. I have tried the RETURNING but, it is retrieving the list of records processed either in the INSERT or the UPDATE.
Am I missing something or it is not possible with this syntax?
Thanks in advance
Johann
On 02/15/2016 01:54 AM, Johann Kerdal wrote: > Hello, > > I am trying to use the INSERT ON CONFLICT syntax to build an SCD 2 table > loader. > > here are the behaviors I need to achieve: > SCD 2 table: TAB_OUT > new candidate records: TAB_IN > > The table TAB_IN contains the new candidate records to be submitted to > TAB_OUT. > *Case 1*: The record presented by TAB_IN is already present in TAB_OUT > and the fields were not modified > -> DO NOTHING > > *Case 2*: The record presented by TAB_IN is not present in TAB_OUT > -> INSERT NEW RECORD (*OK*) > > *Case 3*: The record presented by TAB_IN is already presente in TAB_OUT > and the fields were modified > -> CLOSE existing record in TAB_OUT(*OK*) > -> /INSERT new RECORD (*KO*)/ > / > / > I can't find a way to retrieve the list of records that were updated ON > CONFLICT. I have tried the RETURNING but, it is retrieving the list of > records processed either in the INSERT or the UPDATE. I was with you until Case 3. Are you trying to UPDATE the existing record with a closed flag and INSERT the new modified record? > > Am I missing something or it is not possible with this syntax? What is the command you are using? > > Thanks in advance > > Johann > > -- Adrian Klaver adrian.klaver@aklaver.com