Click here to monitor SSC
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
William Plourde
William Plourde
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 349
I have two databases and I have to move data from approx 34 tables from one db to another. Both dbs have the exact same structure. This work will be accomplished via stored procedures and a c# windows service.

The first part of the solution is to copy the necessary table data from the source db to a set of staging tables in the target db. The second part of the solution is to copy the data within the target db from the staging tables into the live tables. The staging tables have the same structure but no identities or fk constraints.

This is where I'm having some trouble figuring out the best way to do it.

Each table has an identity (bigint) and one or two foreign keys (bigints). The identity and fk values were all good in the source db, but as rows are inserted into the live tables from the staging tables the identity columns are assigning new values. This is causing a cascade of problems because I have to maintain a mapping table for each table to track the old vs the new Id's on the fly.

So for each table to process I would have to iterate over each row, save it to the the live table, then write a table specific mapping row with the old Id and the new id. So if each table to process also has an id mapping table I'm now up to 68 tables.

When finished all the newly inserted rows should relate to each other the same as the source db they came from even though their actual id values will be different.

This seems like a very 'brute force' method of doing this.

Does anybody have any experience doing anything similar, or know of a methodology that would help ?

Personally I think it may be a better design choice to have used GUIDs instead of integers for the primary keys. This way we could just blindly copy a bunch of rows between tables in the different dbs and it should work without having to change/remap any ids.

But as of right now we have integer ids. Other than changing our design to use GUIDs does anybody have any ideas that would make this process less painful ?
Greg Charles
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5827
Are you appending data to the live tables in the destination database or are you replacing the data with what is in the source database?

Greg
William Plourde
William Plourde
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 349
Greg Charles (6/19/2013)
Are you appending data to the live tables in the destination database or are you replacing the data with what is in the source database?


The process is appending/adding rows to the tables in the target db. No updates of existing rows.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19512
I would suggest that you need to change the design of the target database so that you do not have identity columns as PKs there. Just make them standard INT or BIGINT columns.

Instead, retain the values from the source database. That will makes your updates so much easier, as row nnn in the source will map to row nnn in the target.


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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19512
Also, this sounds like it might be a task for SSIS - have you considered that as an alternative?


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
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 349
Phil Parkin (6/20/2013)
I would suggest that you need to change the design of the target database so that you do not have identity columns as PKs there. Just make them standard INT or BIGINT columns.

Instead, retain the values from the source database. That will makes your updates so much easier, as row nnn in the source will map to row nnn in the target.


thanks but that's not really an option. Due to business requirements both databases need to be the exact same structure, including identities. Basically we're balancing our data load across multiple dbs and keeping a master index of which db a person is stored in. Its a form of data balancing and allocation. So as the data grows we can assign new people or move people between dbs. In the future there will be a small group of dbs that store all the people.
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 2848
If the data is going one way, from DB A to DB B and not going the other way the change the tables using identity fields by dropping the identity option of DB B. Instead of using a stored procedure and C# application to move data, which can place an added load on the process, use SQL Server Transactional Replication. This will keep DB B looking like DB 1. Maintenance is low and transactions are quick. Replication operates on data manipulations which show up in the transaction log and do not need to search using a select for any data changes which you are probably doing with your stored procedure.

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
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 349
sjimmo (6/20/2013)
If the data is going one way, from DB A to DB B and not going the other way the change the tables using identity fields by dropping the identity option of DB B. Instead of using a stored procedure and C# application to move data, which can place an added load on the process, use SQL Server Transactional Replication. This will keep DB B looking like DB 1. Maintenance is low and transactions are quick. Replication operates on data manipulations which show up in the transaction log and do not need to search using a select for any data changes which you are probably doing with your stored procedure.


I've never used transaction replication so thanks for the idea, I'll do some reading and see if it can help in this situation. But I do not have have the option of dropping any identities. For the sake of argument, assume we have 6 possible dbs. The business requirement is that a person (all their data across all tables) can be moved from the db they are currently 'homed' in to any other db.

These db's may be on the same server or different servers. As the system grows new db's will be added as necessary. Our master index holds the configuration and connection information so we know the pool of dbs, where they are located, and the connection information for each db. Basically we are load balancing our data. There are business and technical reasons for doing this.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8411 Visits: 19512
William Plourde (6/20/2013)
Phil Parkin (6/20/2013)
I would suggest that you need to change the design of the target database so that you do not have identity columns as PKs there. Just make them standard INT or BIGINT columns.

Instead, retain the values from the source database. That will makes your updates so much easier, as row nnn in the source will map to row nnn in the target.


thanks but that's not really an option. Due to business requirements both databases need to be the exact same structure, including identities. Basically we're balancing our data load across multiple dbs and keeping a master index of which db a person is stored in. Its a form of data balancing and allocation. So as the data grows we can assign new people or move people between dbs. In the future there will be a small group of dbs that store all the people.


So a person could move from db A, to B to C to D etc?


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
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

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