Importing Data With Duplicate Keys

I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!No.
You will have to update the keys in two or your Access databases to ensure that no records from the three databases conflict. And you will likely also have to deal with issues where you have identical records in each database that should be merged rather than duplicated (such as in lookup lists...).
Or, another possible solutions is to load the data from separate databases into separate versions of the same SQL Server table, assigning new GUID identifiers simultaneously. The three tables can then be merged using GUIDs rather than the integer/identity values you probable have right now. Note that you will still need to update foreign keys in related tables to reference the new GUIDs.
These things are all just part of a DBA's day...|||Blah, I knew this wouldn't be fun. Thanks for the info.|||want to show us some DDL and sample data of what the problem is?|||I never said it wouldn't be fun. I just said it is what we DBAs do all day.

Every day.

Day in, day out...

Day after day after day after day...

Hell, it's a blast.|||i have 2 contradictory seanisms on this...

I sometimes tell the bellachers around here

"if you ain't having fun, go do something else for a living"

and when they complain sometimes I tell them...

"if it was easy and fun they would not pay us money to do it"|||I figured out an easy way to do this. Basically I check the ID of the last field in my master database. Before I import data I remove the primary key restriction and run an update query whereby I add that last number to each of the existing ID numbers. This automatically iterates my keys to the new start point I need.|||Fine. Now make sure you update all the foreign key references to the primary keys you just modified...|||I forgot to mention to do that immediately afterward, thanks.|||sounds like a total fubar process

how does a surrogate key have any meaning?|||I admit, it's not the best solution--if I understood what you said. But we're only talking about three tables.

Importing data from one source in two destination tables linked by a foreign key

Hi,

I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :

table MOTHER (MOTHER_ID, MOTHER_NAME)

table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)

After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.

My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".

The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...

Is there a solution to chain two insertions with a foreign key constraint in a data flow?

Thanks

Regards,

Arnaud Gervais.

Just create two data flows in your control flow. The first to load the mother table. The second to then load the child table.|||

I can do that but I have a unique source file and with this solution I have to do same transformations of my data 2 times...

I've found a better solution that seems to be often used: http://blogs.conchango.com/jamiethomson/archive/2006/05/22/3974.aspx

It consists on creating two data flow and chaining them on the control flow as following:

- first data flow : I transform data, insert them in the mother table and keep the data flow in a raw file using multicast component.

- second data flow : I read the raw file to get transformed data, and find the foreign key with a lookup component bound to mother table previously imported.

It seems to be the best and fastest solution to do that...

Thanks,

Arnaud.

|||

it sounds like you are talking about the same thing at this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=918028&SiteID=1

Jamie Thomson has posted feedback asking for "Intrinsic Flow Priority" to be included in SSIS. This would mean you could achieve what you are trying to do in a single data flow task.

Please go to (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058) and vote for this as well as posting your use case.