March 26, 2014 at 3:38 pm
I'm hoping someone can help find an easier, more supportable way of doing the following:
1. Take a subset of data from about 100 tables that have multiple references to other tables in this group of 100 from a first DB.
2. Insert the above data into a second DB, a database that already has data in the 100 tables, while maintaining the correct references.
As a general approach, the best way I can think of doing this is as follows:
1. Create mapping tables for every ID that is referenced in a different table (OldID NewID)
2. Insert the old data into the new table and output the OldID and NewID into the mapping table.
3. Use that mapping data to make sure all tables that use those IDs have the new IDs in DB2.
This approach is extremely labor intensive both on initial implementation and would require a fairly substantial amount of work to maintain going forward. Does anyone know of a better approach to do this?
March 27, 2014 at 12:14 am
You could (protect your identity by 😉
1. Script the database
2. Run the create table part only at the destination
3. Use Import wizard to transfer the data, enable identity insert and filter where needed
4. Run the constraint part of the database script
or
1. Copy database
2. Script the constraints and drop them
3. Clean the data
4. Re-Create the constraints
Hint, look up IDENTITY_INSERT
😎
March 27, 2014 at 9:32 am
Thanks for the reply, but I think you're missing one part of my scenario or I'm not quite understanding your answer. The problem with your answer is that I want to take data from one database to another database that already has data in tables that the data is going into.
For example,
DB1 - Table1 has IDs 1-1000
DB2 - Table1 has IDs 1-500
I'm wanting to add records with IDs 1-1000 to the second table but also making sure the references in other tables point to the correctID from Table1.
March 27, 2014 at 11:09 am
ErikMN (3/27/2014)
Thanks for the reply, but I think you're missing one part of my scenario or I'm not quite understanding your answer. The problem with your answer is that I want to take data from one database to another database that already has data in tables that the data is going into.For example,
DB1 - Table1 has IDs 1-1000
DB2 - Table1 has IDs 1-500
I'm wanting to add records with IDs 1-1000 to the second table but also making sure the references in other tables point to the correctID from Table1.
My bad here, misunderstood the question. My thought is to update all references as part of the load, a mapping solution might become very hard to maintain.
Are there overlaps in the referential data?
March 27, 2014 at 12:58 pm
ErikMN (3/27/2014)
...I want to take data from one database to another database that already has data in tables that the data is going into.For example,
DB1 - Table1 has IDs 1-1000
DB2 - Table1 has IDs 1-500
I'm wanting to add records with IDs 1-1000 to the second table but also making sure the references in other tables point to the correctID from Table1.
OK, here's my crazy, shoot-from-the-hip shortcut idea:
1) Make a new schema in your source DB called [Export]
2) For each table to be exported, create a View in [Export] named "v_{tableName}" that just selects every column by name. (This can be automated)
3) Edit these views, changing every ID column and ID-linking column to "{IdName}+20000 As [{IdName}]". (with some effort, this too can be automated)
4) Use Generate Scripts on the source DB, Data-Only and select only the Views in the [Export] schema.
5) Edit the script and add "SET IDENTITY INSERT" On/Off statements for every table's INSERT statement block. (I haven't figured out a way to automate this part)
6) Backup the Target DB. Seriously, this is dangerous.
7) Execute the script against the Target DB.
And hopefully that does it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2014 at 1:02 pm
If I understand what you mean by overlaps in the referential data, then yes there are. Without going into the specifics, both databases have a table called Person with PersonID as an identity. That identity is referred to in a number of other tables and it is not unique between both databases so there might be a PersonID = 500 in both databases that refer to different persons.
The only approach that I can think of, that looking back at the OP, I didn't explain very well, would be to update the data as it goes in. The mapping tables I was referring to would map the ID in the old database to the ID that was added in the new database, so that when the data of a table that refers to PersonID is inserted, it would put in the new PersonID.
Example mapping table:
CREATE TABLE dbo.MapPerson(
OldPersonID INT,
NewPersonID INT)
Then when inserting into a new table that has a reference to PersonID, I would join in the MapPerson table to get the new PersonID. As you said, this approach would be very labor intensive and get more so as you add more tables with multiple references to other tables. That's why I'm hoping there might be another approach I haven't thought of...
March 27, 2014 at 1:19 pm
RBarryYoung (3/27/2014)
OK, here's my crazy, shoot-from-the-hip shortcut idea:1) Make a new schema in your source DB called [Export]
2) For each table to be exported, create a View in [Export] named "v_{tableName}" that just selects every column by name. (This can be automated)
3) Edit these views, changing every ID column and ID-linking column to "{IdName}+20000 As [{IdName}]". (with some effort, this too can be automated)
4) Use Generate Scripts on the source DB, Data-Only and select only the Views in the [Export] schema.
5) Edit the script and add "SET IDENTITY INSERT" On/Off statements for every table's INSERT statement block. (I haven't figured out a way to automate this part)
6) Backup the Target DB. Seriously, this is dangerous.
7) Execute the script against the Target DB.
And hopefully that does it.
Thank you, this is definitely intriguing and while it would still be quite a bit of work, it'd certainly be less initial work than my original approach. The only thing I'd need to think about is step 3 where i don't think a static value of +20000 would be appropriate as there are tables with a few hundred million rows and some with 20, so I'd probably want to do some kind of count or max on the receiving table and add that instead.
April 3, 2014 at 4:14 pm
ErikMN (3/27/2014)
RBarryYoung (3/27/2014)
OK, here's my crazy, shoot-from-the-hip shortcut idea:1) Make a new schema in your source DB called [Export]
2) For each table to be exported, create a View in [Export] named "v_{tableName}" that just selects every column by name. (This can be automated)
3) Edit these views, changing every ID column and ID-linking column to "{IdName}+20000 As [{IdName}]". (with some effort, this too can be automated)
4) Use Generate Scripts on the source DB, Data-Only and select only the Views in the [Export] schema.
5) Edit the script and add "SET IDENTITY INSERT" On/Off statements for every table's INSERT statement block. (I haven't figured out a way to automate this part)
6) Backup the Target DB. Seriously, this is dangerous.
7) Execute the script against the Target DB.
And hopefully that does it.
Thank you, this is definitely intriguing and while it would still be quite a bit of work, it'd certainly be less initial work than my original approach. The only thing I'd need to think about is step 3 where i don't think a static value of +20000 would be appropriate as there are tables with a few hundred million rows and some with 20, so I'd probably want to do some kind of count or max on the receiving table and add that instead.
In that case, if the data type is int, add +1000000000 instead of +20000 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply