1

i have a requirement to combine two similar database into one for eg

i have two different database of app version 1.0 looks like this

company 1 table

    id Name Address  state
    1  aaa  street2  CA
    2  bbb  street2  CA   


company 2 table

    id Name Address  state
    1  ccc  street2  CA
    2  ddd  street2  CA

now i want to merge the two different database into one i app version 2 app version 2 table looks like this

id company_id name Address state
1   c1        aaa  street2  CA
2   c1        bbb  street2  CA      
3   c2        ccc  street2  CA
4   c2        ddd  street2  CA

in both versions id column is identity column with auto increment

problem here is if i move the data to version 2 table my id's will change if it happens how to modify the child table data which refers to this id.

is there any tool available readily to handle this or we need to write our own script to accomplish this i have tons of tables like this to migrate from version 1 to version 2

any help on this will be appreciated.

Thanks.

Chandu
  • 81,493
  • 19
  • 133
  • 134
muthu
  • 87
  • 1
  • 12
  • Related thread- http://stackoverflow.com/questions/909541/how-to-merge-two-databases-in-sql-server – KV Prajapati Nov 11 '11 at 05:04
  • Is this a once off migration or will it be done on an ongoing regular basis? Is it for an existing system or functionality required for new system? If it is once off I would just write a script and re key the id columns. – Craig Nov 11 '11 at 05:09

3 Answers3

0

In the keys identities in both table ,You must use Old id to it.Because you need to secure all the data without use Old id you can't retrive old values

TechGuy
  • 4,298
  • 15
  • 56
  • 87
0

Since the ID in both tables are identities you securely will have customer from both companies with the same ID: I suggest you create a new ID for App2 this can be also identities, and store the old id of the user in order to keep the relationship with the other tables. By example you neew table must be as follows:

NEW ID  old_id company_id name Address state
100     1       c1        aaa  street2  CA
101     2       c1        bbb  street2  CA      
102     1       c2        ccc  street2  CA
103     2       c2        ddd  street2  CA

I don't know if ther is a tool that does it automatically.

Ernesto Campohermoso
  • 7,213
  • 1
  • 40
  • 51
0

The ease or difficulty of this process entirely rests on the quality of the schema that was setup. Let's start with worst to best:

The Identity column is used by the users.

This is the worst case scenario and it violates a fundamental rule with respect to surrogate keys (In this case the Id column): they should be hidden from the user. If the user is allowed to see, use, print on output, stamp on physical items the identity value, then your task is much more difficult. The only reasonable solution is to add a column which indicates the source of the system and modify the unique or primary key constraint on the identity column to include this new column. In this way, your data would look like:

id   company_id  name       address     state
1    c1          aaa        street2     CA              
2    c1          bbb        street2     CA
1    c2          ccc        street2     CA              
2    c2          ddd        street2     CA

What makes this solution so awful is that you can no longer use the Identity attribute, all business layer and interface code must change to pass the company_id as a parameter when saving a value to this table and any reports will have to be reviewed to determine if they will break or how they will break. In addition, all foreign key relationships would have to be changed along with all code that writes to those tables. It's a right ole' mess.

Identity column not used by users but no business key.

A "business key" as I'm calling it, is a unique constraint on a set of columns in the table other than the Identity column which is acting as a surrogate key. This violates a fundamental rule with respect to surrogate keys and data consolidation is one area where it really rears its ugly head. The simplest solution is to temporarily add a column to the table that holds the previous system's PK and use that to import the related tables.

Alter Table CompanyName
    Add Column LegacyPk int null 
GO
Insert NewTable( company_id, name, address, state, LegacyPk )
Select company_id, name, address, state, id
From company_2.Table

Insert SomeChildTable(....
Select ...
From NewTable
    Join OldChildTable
        On OldChildTable.ParentId = NewTable.LegacyPk

Identity column is not used by users and a business key exists.

This is the best situation. In this scenario, the surrogate key is used as it should be and you can use the business key for your joins:

Insert NewTable( company_id, name, address, state )
Select company_id, name, address, state
From company_2.Table As C2
    Left Join company_3.Table As C1
        On C1.KeyCol = C2.KeyCol
Where C1.KeyCol Is Null
Thomas
  • 63,911
  • 12
  • 95
  • 141