SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / Best way to move data from one db to another and maintain relationships ? / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 03 Mar 2015 13:34:33 GMT20RE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxWell, 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 ;-)Fri, 21 Jun 2013 07:40:10 GMTPhil ParkinRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]Phil Parkin (6/21/2013)[/b][hr]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.[/quote]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.Fri, 21 Jun 2013 07:00:52 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxNo 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.Fri, 21 Jun 2013 06:44:54 GMTPhil ParkinRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote]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.[/quote]Ok, I understand what your trying to accomplish, but introducing changes to x[sup]n[/sup] 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.Fri, 21 Jun 2013 06:36:27 GMTDiverKasRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]DiverKas (6/20/2013)[/b][hr][quote][b]William Plourde (6/20/2013)[/b][hr][quote]So a person could move from db A, to B to C to D etc?[/quote]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.[/quote]Just curious, why different DB's? Why not partition the tables instead and save the headache?[/quote]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.Thu, 20 Jun 2013 14:32:53 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]William Plourde (6/20/2013)[/b][hr][quote]So a person could move from db A, to B to C to D etc?[/quote]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.[/quote]Just curious, why different DB's? Why not partition the tables instead and save the headache?Thu, 20 Jun 2013 13:49:47 GMTDiverKasRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]sjimmo (6/20/2013)[/b][hr]I don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.[/quote]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.Thu, 20 Jun 2013 11:11:07 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxI don't think transactional replication is going to do it for you. Maybe merge. But those identity fields are going to kill you.Thu, 20 Jun 2013 11:06:56 GMTsjimmoRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote]So a person could move from db A, to B to C to D etc?[/quote]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.Thu, 20 Jun 2013 07:25:33 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]William Plourde (6/20/2013)[/b][hr][quote][b]Phil Parkin (6/20/2013)[/b][hr]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.[/quote]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.[/quote]So a person could move from db A, to B to C to D etc?Thu, 20 Jun 2013 07:22:25 GMTPhil ParkinRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]sjimmo (6/20/2013)[/b][hr]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.[/quote]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.Thu, 20 Jun 2013 06:56:22 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxIf 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.Thu, 20 Jun 2013 06:46:34 GMTsjimmoRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]Phil Parkin (6/20/2013)[/b][hr]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.[/quote]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.Thu, 20 Jun 2013 06:30:39 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxAlso, this sounds like it might be a task for SSIS - have you considered that as an alternative?Thu, 20 Jun 2013 00:35:00 GMTPhil ParkinRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxI 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.Thu, 20 Jun 2013 00:33:53 GMTPhil ParkinRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspx[quote][b]Greg Charles (6/19/2013)[/b][hr]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?[/quote]The process is appending/adding rows to the tables in the target db. No updates of existing rows.Wed, 19 Jun 2013 15:11:28 GMTWilliam PlourdeRE: Best way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxAre you appending data to the live tables in the destination database or are you replacing the data with what is in the source database?Wed, 19 Jun 2013 15:09:59 GMTGreg CharlesBest way to move data from one db to another and maintain relationships ?http://www.sqlservercentral.com/Forums/Topic1465340-3077-1.aspxI 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 ?Wed, 19 Jun 2013 12:58:07 GMTWilliam Plourde