Sql-server – How to deal with FK constraints when importing data using DTS Import/Export Wizard

sql serversql-server-2012

I am trying to use the SQL Server Import and Export Wizard to copy data from my production db to my dev db but when I do it fails with the error "The INSERT statment conflicted with the FOREIGN KEY constraint" i have over 40 tables with lots of FK constraints, is there some easy way to deal with this without having to write a drop constraint/add constrat script?

I just found out that in Web edition of SQL Server, which is what I am running, DTS will not let you save packages.

Best Answer

I was given this solution over at SQLTeam.com:


 EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Then import your data

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Using that method I was able to import all the data in with no issues.