About EasyMorph Tutorials & Examples Web-help

Mirror data between tables (without truncate)

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

  • 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

The “Delete matching database rows” action does exactly that.

image

  • Identify rows to update
  • Delete them using the “Delete matching DB rows” action and then re-insert with correct data
To learn more about EasyMorph visit easymorph.com.