SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


To merge several tables into one


To merge several tables into one

Author
Message
tania 56573
tania 56573
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
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.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16782 Visits: 19121
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
tania 56573
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16782 Visits: 19121
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
DiverKas
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 460
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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16782 Visits: 19121
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
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28542 Visits: 39977
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
tania 56573
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
Thanks a lot to all.

It is still foggy but at least I can start with something in mind.
Neel 7777
Neel 7777
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 84
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
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12439 Visits: 10691
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search