Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

To merge several tables into one Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 9:50 AM
Points: 3, Visits: 10
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.
Post #1460333
Posted Wednesday, June 5, 2013 11:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,312, Visits: 7,142
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460350
Posted Wednesday, June 5, 2013 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 9:50 AM
Points: 3, Visits: 10
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?
Post #1460370
Posted Wednesday, June 5, 2013 12:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,312, Visits: 7,142
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460372
Posted Wednesday, June 5, 2013 12:34 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.
Post #1460375
Posted Wednesday, June 5, 2013 12:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,312, Visits: 7,142
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1460381
Posted Wednesday, June 5, 2013 1:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 12,876, Visits: 31,788
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1460394
Posted Wednesday, June 5, 2013 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 9:50 AM
Points: 3, Visits: 10
Thanks a lot to all.

It is still foggy but at least I can start with something in mind.
Post #1460422
Posted Sunday, June 23, 2013 10:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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.
Post #1466573
Posted Wednesday, June 26, 2013 8:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 1,595, Visits: 4,584
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.
Post #1467718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse