Question

Merge identical databases

  • 18 October 2017
  • 1 reply
  • 3 views

Hi, I would like to know if the following is possible and how:

I have several identical Access backend databases (one for each client, same scheme - different data, several relationships, links and foreign keys). I would like to merge all of them to one MS SQL database and add an identifier for each client data. One time only!

How to handle original relationships? Example: InvoiceID and InvoiceLine for each original database.

I’m talking about Access DB, but I could convert them all to MSSQL and then merge…

All front ends will connect to this database, but each client will be able to see only each own data.

Any help? Thanks!

Jairo


1 reply

Userlevel 3
Badge +13

Hi @jairozeitel, you can read in your MS Access database and write it out to a SQL Server database. FME will create the tables for you if they do not exist. For things like constraints such as your foreign keys, I would recommend using the SQL After Translation (available in our betas) to implement such things. If 2018 isn't available to you, you can use the FeatureWriter to write to the tables. Once features are written, the FeatureWriter will output a summary feature which can be used to trigger a SQLExecutor where you've create a SQL statement to add the constraints, keys, etc.

Reply