SQL 2005 export problem - Identity Insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SQL 2005 export problem - Identity Insert

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2007-08-21 : 21:59:15

Hi there,
Having problems exporting data from a database (sql2000 db) to a development db (also sql2000) using SQL 2005 Server Studio and Import/Export wizard (error: Violation of PRIMARY KEY constraint <keynamehere>. Cannot insert duplicate). The destination table has no records in it.

I followed the instructions here:

----------------------------------------------------
Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

Then use the export wizard on 2005,

select all tables and views

Edit Mappings and check the Enable Identity Insert box

UN check the Optimise for many tables box

Deselect ALL the views so that it is only tables that are copied

Run the export ignoring the warning

After the export is complete

Run the following on the 2000 database

exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
----------------------------------------------------

but it gives me:
Violation of PRIMARY KEY constraint <keynamehere>. Cannot insert duplicate.

Ive burnt a few hours on this now and thinking there must be something I am missing!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-21 : 22:37:25

Tried in em?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources