Merging 2 SQL Server 2000 DB's

[MikeHunt74]

Hi all,

we currently have a CRM application which uses SQL Server 2000 for its backend data storage. The CRM has 4 different databases, all with the same schema.

My task is to merge all these db's together without losing any information.

as part of the merge process we wish to identify duplicate companies based on address or post code and merge all the data together, including all contacts, notes and histories.

is all this possible with DTS, if so, how? or am i going to have to develop a software app to do this?

Thanks in advance for any help received.

 

[philhege]

The complexity of this task depends on the schema and methods that the CRM application uses to store and relate its data. You'll have to understand all of the relationships, create properly-formatted keys, and define what fields need to be evaluated to be considered "duplicates".

Some CRM packages use proprietary GUIDs for keys. Some have hashing algorithms that prevent updates from outside the application. It may be worth your while to check with the vendor to see if your task is possible to begin with.

My US$.02

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SQLSister]

It is certainly possible in DTS. However, you find that one of your biggest issues will be that primary key fields may have the same id in differnt databases relating to different clients. You will need to see if this is the case before going any further and if so, must build sometype of crossrefernce table to be able to relate the old id to the new one, so that all related tables will be imported to the consolidated database withthe correct new id.

Once you have this cross refernce table it can also be used as part of your process to merge records. The difficulty in merging records will be that you may run into issues with unique indexes. For instance suppose you have two vendors that are the same in two databases. When you go to add all the details for the vendor in the second database, some of the information such as phone number and address may already be in there and you would not want to put it in twice and some may not be there (say fax number) for one record but not the other. You will need to write your T-SQL code to look for these types of things.

I would be very carful of merging just based on address or postal code. Not knowing your data, it is hard to say if there are issues, but at least include name! Even then if you have people, you still might not have a dup because names are not unique identifiers.

"NOTHING is more important in a database than integrity." ESquared