import export wizard "violation of primary key" sql 2005

[mbde]

I am trying to use the import export wizard to move data from sql 2000 to sql 2005. I am moving about 20 tables so I choose optimize for multi table and run in transaction. I highlight all 20 tables and choose Edit Mappings, then check of Delete existing rows from table. I am currently running SP2.
When I do this I get a violation of PK in the first table. There is none as that key also exist on the 2000 server. To prove that I ran truncate table statements then ran the package and it runs fine!
Have any of you ever experienced this and have you been able to come up with a solution?

 

[SQLSister]

I would normally run the main table first in a separate transaction before doing child tables. I've seen it happen where the script created by a wizard was in aphabetical order and it tried to insert to the child table before the parent table was populated. Or the inserts to both tables were inteh same transaction so the key was violated becasue the data had not committed yet.

You may have something like this going on. I always make sure that I know waht order things need to be entered in another database and then run only groups of data that can be done in the same transaction without violating keys. It really is best not to rely on wizards for data transfers of this nature.

Questions about posting. See faq183-874

 

[mrdenny]

At the bottom of the import / export wizzard there is a check box which will allow you to run all the imports in a single transaction.

Give that a shot. If you use that method referential integritary isn't really an issue because nothing is commited until all the data for all the tables is written.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com