To merge several tables into one

Question

To merge several tables into one

tania 56573

SSC Journeyman

Points: 83
More actions
June 5, 2013 at 10:49 am

#278291

Hi there,
My company has several instances of the same app. One app/database per client. Now maintenance has become a nightmare. I want to combine all into one. The problem is how to merge tables with the some identical keys. What is the best practice? Sorry if it's not clear, I'm not a SQL person.
Any suggestion would be helpful.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That sounds like a lot of work to do when you think about it.

You should either add a Client ID to each table to preserve uniqueness when merging all the databases. You must add this to your code every time you call one of these tables.

Another option would be to add a prefix (to char based keys) or a base number (to int keys). But this will change all of your keys and have to review the code.

You won't find an easy option for this project and it might take you a while to test everything.

An option would be to automize the administration. It won't fix the problem but it will give you more time to implement the changes.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tania 56573 SSC Journeyman Points: 83 More actions · Answer 2

Thanks for quick reply. Is it a good idea if I reindex tables starting from some number before merging? Let's say in the first database table Entities Entity_Id is in a range from 1 to 1000, so I will reindex table Entities in the second database to a range from 1001 to 2000? Or this is a stupid idea?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

But you have to check all of your dependant tables to update the values. An option is to check your Foreign Keys are specified as ON UPDATE CASCADE.

It's hard to go into many details, because I might be giving an inadecuate solution due to the lack of information (and I'm not sure that a forum will give you complete answers to this scenario), so you better test before dealing with the real data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DiverKas SSCrazy Points: 2049 More actions · Answer 4

Assuming you have down time, my suggestion would be:

Merge all your tables into one table with no constraint, with an additional column indicating the source database/table it came from. Add a new IDENTITY column (OR UNIQUEIDENTIFIER if your going that route). Then you can simply update your ancillary tables with the new ID, joined on the old ID. Then drop the source column and old ID and its done.

This isnt that hard, and you would need downtime to accomplish it.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

DiverKas (6/5/2013)
Assuming you have down time, my suggestion would be:
Merge all your tables into one table with no constraint, with an additional column indicating the source database/table it came from. Add a new IDENTITY column (OR UNIQUEIDENTIFIER if your going that route). Then you can simply update your ancillary tables with the new ID, joined on the old ID. Then drop the source column and old ID and its done.
This isnt that hard, and you would need downtime to accomplish it.

Is not that hard for a single table, but as far as I understand, the idea is to migrate all tables from multiple databases.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 6

luis covered a couple important points i wanted to mention again: you'll need a new column for the ClientId in there somewhere.

we've done this in the past, and the way we tackled it was adding temporary columns to the end of the tables, that capture the old PK and FK values.

we insert into one table that has an identity() column for the new PK, and make sure the previously existing PK gets placed in the temp columns. with that in place, we can insert into child tables, by getting the new PK from the table and joining it against the migrating table(s) against the previous PK's;

our database didn't have data going deeper than three levels of PK/FK relationships, but we still had to migrate EVERY tables in FK hierarchy order, and also, comparing lookup tables(like user defined statuses) against values instead of PK, so the table TBCITY might have the value 'Miami' in two databases

but with different PK's assigned to them. those tables, and the values that reference them, need to point to a new combined table instead.

when it's all migrated, we can drop the columns that were holding old PK's.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tania 56573 SSC Journeyman Points: 83 More actions · Answer 7

Thanks a lot to all.

It is still foggy but at least I can start with something in mind.

Neel 7777 Right there with Babe Points: 778 More actions · Answer 8

First create a development environment. Attach all the database in a local machine in a single instance. Now take time and have plan for the identical keys. May be add field for new key and populate.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

tania 56573 (6/5/2013)
Hi there,
My company has several instances of the same app. One app/database per client. Now maintenance has become a nightmare. I want to combine all into one. The problem is how to merge tables with the some identical keys. What is the best practice? Sorry if it's not clear, I'm not a SQL person.
Any suggestion would be helpful.

I'd suggest adding Client_ID to all of your tables. So instead Order_ID being the primary key for Orders table and foreign key on Order_Items tables, it would now be a composite key of Client_ID, Order_ID. The primary advantage of this is that the existing key values don't change, which will retain transactional history. Also, since consolidating data across clients is being done in the physical model for maintainability reasons (the client entities themselves are not merging in the real world), the case usage and queries of the application will tend to be client specific.

For example, client ABC, or perhaps the sales representabive for client ABC, will login to the application and want to see sales transactions only for ABC. Having Client_ID in your transactional tables will help facilitate this.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho