1

I have two projects using the same data. However, this data is saved in 2 different databases. Each of these two databases has a table that is almost the same as his counterpart in the other database.

What I am looking for

I am looking for a method to synchronise two tables. Easier said, if database_one.table gets an insert, that same record needs to be inserted into database2.table.

Database and Table One

Table Products
| product_id | name | description | price | vat | flags |

Database and Table Two

Table Articles
| articleId | name_short | name | price | price_vat | extra_info | flags |

The issue

I have never used and wouldn't know how to use any method of database synchronisation. What also worries me is that the tables are not identical and so I will somehow need to map columns to one another.

For example:

database_one.Products.name -> database_two.articles.name_short

Can someone help me with this?

Peter
  • 8,776
  • 6
  • 62
  • 95
  • There are ways to solve this - like triggers etc, but why getting there in the first place. You can normalize the "Articles" table, and by that insert only to one table. – Yosi Dahari Dec 04 '15 at 08:47
  • @YosiDahari This is just an example. The actual databases handle data on a large scale. It's near to impossible to convert all my code to use a different database to get the data. So I need it to be synced. – Peter Dec 04 '15 at 08:50
  • How about [Triggers](http://stackoverflow.com/questions/15975877/mysql-trigger-on-insert-update-events)? – Yosi Dahari Dec 04 '15 at 08:52
  • @YosiDahari Care to elaborate on this in an answer? – Peter Dec 04 '15 at 09:09

2 Answers2

1

One of possible method is to use triggers. You need to create trigger for insert, update and delete on database_one.table, that does coresponding operation on database2.table. I guess, that there won't be any problems with insert/update/delete between both databases. When using triggers, you can very easily map columns. However you need to consider prons and cons of using triggers - read something here or here. From my experience performance is very important, so if you have heavy loaded DB it is not a good idea to use triggers for data replication. Maybe you should check this too?

Community
  • 1
  • 1
Maciej Piaseczny
  • 59
  • 1
  • 4
0

You can use MERGE function: https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

Then create a procedure that runs at desired frequency or if it needs to be instant insert the merge into a trigger.

Andrej Skripic
  • 79
  • 4