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


Best way to move data from one db to another and maintain relationships ?


Best way to move data from one db to another and maintain relationships ?

Author
Message
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3744 Visits: 2903
I don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
William Plourde
William Plourde
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 349
sjimmo (6/20/2013)
I don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.


Yes, they are killing me. I had to add a [NewId] field to each table, iterate thu each table saving rows individually so I can capture the [NewId] value, then save that back to the staging table row. Then for related staging table data I have to join on old ids within the staging tables, but use the [NewId] value(s) when I insert them into the live tables.

It's straight-forward enough but is a very brute force methodology.
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
William Plourde (6/20/2013)
So a person could move from db A, to B to C to D etc?


Yep, that is correct. Assuming 6 dbs out in the future. A person (and all their related data) can move from any db to any other db. Possibly more than once over time due to business logic.


Just curious, why different DB's? Why not partition the tables instead and save the headache?
William Plourde
William Plourde
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 349
DiverKas (6/20/2013)
William Plourde (6/20/2013)
So a person could move from db A, to B to C to D etc?


Yep, that is correct. Assuming 6 dbs out in the future. A person (and all their related data) can move from any db to any other db. Possibly more than once over time due to business logic.


Just curious, why different DB's? Why not partition the tables instead and save the headache?


There are some business and potentially legal reasons behind the decision. I have not personally used partitioning but I know what it is and I'll brush up on the topic to see if it can help. But to make a long story short if our system gets large enough we can dynamically add a new database and auto-magically start loading data onto it,even if that db is on another physical server. So it's not quite the same as partitioning. Its more like home grown load balancing for data.
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
There are some business and potentially legal reasons behind the decision. I have not personally used partitioning but I know what it is and I'll brush up on the topic to see if it can help. But to make a long story short if our system gets large enough we can dynamically add a new database and auto-magically start loading data onto it,even if that db is on another physical server. So it's not quite the same as partitioning. Its more like home grown load balancing for data.


Ok, I understand what your trying to accomplish, but introducing changes to xn databases seems problematic at best. Not to mention setting up the required maintenance, backups etc.

Just seems partitioning would be a cleaner, less maintenance intensive way to go.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18333 Visits: 20415
No doubt it's too late to change this, but have you thought about using NewId rather than identity as your (nonclustered) PK in the various tables?

As far as I know, this is guaranteed to be unique across the distributed instances and could therefore become the 'permanent' PK for a row, regardless of database.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
William Plourde
William Plourde
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 349
Phil Parkin (6/21/2013)
No doubt it's too late to change this, but have you thought about using NewId rather than identity as your (nonclustered) PK in the various tables?

As far as I know, this is guaranteed to be unique across the distributed instances and could therefore become the 'permanent' PK for a row, regardless of database.


fwiw, I agree with you. I think using guid pks with NewId() for a default value is probably the best way to go. If this were the case I should be able to select all the data for a person from one db and just save it to another db. From what I understand guids are the way to go when consolidating data from multiple dbs.

For some reason our team is really trying to avoid guids as pks. In general we have some bad experiences with guids due to work done in the past where guids were used but were not necessary. I think that's made everyone here gun shy about using them.
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18333 Visits: 20415
Well, if I were you I would suggest it, so that when the pain of maintaining your IDENTITY solution becomes too much to bear, you can always be the smart one who says: I told you so ;-)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
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