Merge Databases

Why it is difficult to merge 2 databases

Download CRM Software Free 60 Day Trial
Download CRM Software Free 60 Day Trial

Merge Databases

Occasionally we hear that a client has, by mistake, been entering Customer Details into 2 separate databases instead of having one Central Database that all users connect to. Then they ask if it is easy to merge the Customer Records and the History information into one database.

Background

Amphis Customer CRM Software can be used by a single user or it can be used by multiple users sharing Customer Data and Documents. To share Customer Data and Documents, you need to move the Customer Database (customer.mdb) and the Documents and Templates folders to a Shared Folder that all the client PCs can access. Then you need to update the Configuration on each client PC from Tools->Configuration to point to the Shared Database and the Shared Documents and Templates folders so that ALL client PCs are connecting to the same Shared Database and Shared Documents and Templates.

Then any information entered on one PC is automatically available on all the other PCs - see Share Data and Documents. You can also Upgrade for Free to use SQL Server Express as the Database for superior performance and scalability - see Upgrade to SQL Server Express.

But if one of the client PCs is left connecting to its local database, then any information entered on that PC is not added to the Central Database and that PC is not sharing Data and Documents with the other PCs.

Merging Data

If there are new Customer Records that have been added to one database but not the other, then it is fairly easy to Export the Customer Records to a spreadsheet in Amphis Customer (from Tools->Data->Export->Customers to CSV File. Then you can delete rows as required to just leave the new Customer Records that don't exist in the other Database and also the header row. Then you can Import them into the other Database BUT you must first delete the Customer Ids from the spreadsheet so that the Customer Records get new Customer IDs when imported into the other database. Otherwise they will be treated as Updates to the existing Customer Records with the same Customer Id.

Here's a much simplified example where there are 2 Customer Databases that have been used. Let's call them Database A and Database B.

Merge Database

Here 3 Customer Records have been created in Database A and 3 Customer Records have been created in Database B. They have the same Customer IDs but they are totally different Customers. Some History entries have been created too but they are totally different in each Database.

Document 200500 in Database A is a Quote for new paving for Acme Paving Group. Document 200500 in Database B is a Quote for a wedding cake for Fairy Cakes and Sweets.

It really isn't easy to merge these 2 databases into one. There cannot be 2 Customers both with Customer Id 100500. Nor can there be 2 Quotes for different Customers but with the same Id.

Import Customers

The best that can easily be achieved is that you could Export the Customers from, say, Database B to a spreadsheet. Then delete the Customer IDs from the spreadsheet. Then Import them into Database A.

This would create the 3 Customers in Database A with IDs 100503, 100504, 100505. (If you left the Customer ID fields in the Spreadsheet it would treat the Import as Updates to existing Customer IDs 100500, 100501 and 100502 which would change the name of the existing Customer Records.

Configuration

Don't forget to change the configuration (in Tools->Configuration) on that client PC so it connects to the shared database.

Download CRM Software Free 60 Day Trial
Download CRM Software Free 60 Day Trial