Mirror data between tables (without truncate)

[jquintard]

Hi there,

I’am looking to mirror records between two databases. The easiest method is to truncate the records in the destination and bulk the data from the source to the destination but this is a bad way, during this actions, users can’t use the remote database.

Suppose I have this records :

LocalTable
ID Name
1 Sophie
2 Laurent
3 Aurélien

RemoteTable
ID Name
1 Sophie
2 Renaud
4 Marc

How I can simply :

• Add records from LocalTable with ID not existing in RemoteTable (like ID 3)
• Delete records in RemoteTable where ID not existing in LocalTable (like ID 4)
• Update records where fields where not strictly equals (like ID 2)
• Do nothing with the rest (or just an update like the previous line)

Do you have an example ?

Jerome

[dgudkov]

@jquintard

Add records from LocalTable with ID not existing in RemoteTable (like ID 3)

• Pull IDs from the local and remote tables into two tables
• Use the "Keep/remove matching" to obtain the difference
• Use the "Select matching database rows" action to pull only the rows with IDs that are different

@jquintard

Delete records in RemoteTable where ID not existing in LocalTable (like ID 4)

The "Delete matching database rows" action does exactly that.

@jquintard

Update records where fields where not strictly equals (like ID 2)

• Identify rows to update
• Delete them using the "Delete matching DB rows" action and then re-insert with correct data