Data Migrations but keeping the relationships intact

Question

2.67/5 (3 votes)

See more:

Hi All

I have two tables

Table1
____________________________
ID | columns I |column II |
----------------------------

Table 2
____________________________
ID | Table I ID | column X |
----------------------------

IDs are the primary key in each table and are set to auto increment. What I need to do is to import data from another DB's tables with exactly the same structure without breaking the relationships.

I have tried to use bulk copy but the ID get changed and the relation ship doesn't hold.

One solution is to import the data in the dataset and then iterate through each row and insert a new row in another dataset and keep a dictionary storing the new and old IDs. But its not efficient as I am dealing with row counts going well above 100,000.

I forgot to mention that I have to do this using C# v3.5

Any pointers ??

Thanx in advance

Posted 1-Apr-11 0:41am

Phan7om

Updated 1-Apr-11 0:47am

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Eduard Keilholz · Answer 1 · 2011-04-01T00:49:00

Solution 1

Insert a record from source table one to destination table one
use select @@IDENTITY to determine the ID of the record inserted in destination table one

insert related records from source table two to destination table two, and related them to the result of the @@IDENTITY

Posted 1-Apr-11 0:49am

Eduard Keilholz

Comments

Phan7om 1-Apr-11 6:53am

If you are talking about inserting directly into the database without using the dataset then that's not feasible as the No of rows more than a couple of 100,000 and it will take days to complete. other wise i think @@identity doesn't apply to dataset. correct me if i am wrong

Regards,

Om Prakash Pant · Answer 2 · 2011-04-01T00:53:00

Solution 2

Set the identity column off and then insert the data. Once the data is inserted, you can set the iedntity as on.

Posted 1-Apr-11 0:53am

Om Prakash Pant

Comments

Phan7om 1-Apr-11 8:00am

Its not working with SqlBulkCopy. What I did is to first set IDENTITY to ON (to explicityly set the ID value you have to set it to "ON") and then tried the bulk copy but got the following error:

Explicit value must be specified for identity column in table 'eDelivery_MailInfo' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

and one more thing I gave datatable as the argument to writetoserver() method of the sqlbulkcopy

Phan7om · Answer 3 · 2011-04-03T20:06:00

Here is the solution that worked for me and that is while creating the "SqlBulkCopy" instance use "SqlBulkCopyOptions.KeepIdentity" and copy both the tables. In this way all the relations will be intact.

//connString is your connection string
SqlBulkCopy bulkCopy = new SqlBukCopy(connString, SqlBulkCopyOptions.KeepIdentity);

Regards