Click here to Skip to main content
15,036,051 members
Please Sign up or sign in to vote.
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
Updated 1-Apr-11 0:47am
v2

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
   
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,
Set the identity column off and then insert the data. Once the data is inserted, you can set the iedntity as on.
   
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
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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900