October 9, 2012 at 1:06 pm
Running SQL 2005 SP4
I am trying to use the Import/Export Wizard to copy 40 table from Server A to Server B. After selecting all the tables, I set them all to "Delete Rows In Destination Table" and "Enable Identity Insert".
I deselect "Optimize for Many Tables" so I can save the package & edit it later.
When I run the package as the last step in the Wizard, it fails and tells me I cannot insert a duplicate - Violation of primary key.
There are no duplicates in the source. "TableA" only had 1 record anyway !
When I run it again, "TableA" completes cleanly, but now I get an error that "TableC" has duplicates.
It seems that the truncates and copies are running at the same time, so sometimes a copy is happening before the truncate and throws an error, and sometimes after the truncate and completes normally.
Any thoughts on this crazy situation ?
EDIT: I saved the package, opened it in BIDS, and see that only 4 of the tables have a "truncate" statement even though in the GUI, they all have "Delete Rows In Destination Table" checked ?!?!? WTF ?!?!
EDIT2: I didn't manually check "Delete Rows In Destination Table" on each table. I highlight all the tables, then choose "Edit Mappings" and check "delete" 1 time and it applies to all the tables ..... except I guess it doesn't really apply to all the tables, just LOOKS like it does, since the check mark is there, but it does not execute.
Is there a workaround for this bug ???
October 10, 2012 at 12:12 am
homebrew01 (10/9/2012)
Is there a workaround for this bug ???
Yes, manually set it for each table 🙂
Or you can just create truncate table statements for each table and paste it into the package.
What you do yourself, is often better 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply