Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

share|improve this question
add comment

4 Answers

Don't drop the constraints.

What you should do is save the SSIS package that the wizard creates, then edit it in BIDS/SSDT. When you edit the package you'll be able to control the order that the tables are processed in so you can process the parent tables then process the child tables when all the parent tables are done.

share|improve this answer
    
That is not really effeciant either, it would take almost an hour right now to modify the package to make sure everything is in the correct order, and even then i can not be sure. This would get more cumbersom as the DB grows and to do it everytime, no thanks. There has to be a simple way of doing this. –  user11512 May 26 '13 at 14:26
add comment
up vote 1 down vote accepted

I was given this solution over at SQLTeamn.com:

Use 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.

share|improve this answer
    
sp_msforeachtable (and sp_MSForEachDb) is undocumented and unsupported. You should not/avoid using it. It might skip the tables !! See this post from @AaronBertrand --> sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/… and this connect item - (MS indicating that they wont fix it) --> connect.microsoft.com/SQLServer/feedback/details/264677/… –  Kin Jun 13 '13 at 16:02
add comment

I think you cannot perform backup and restore from production server as it is a crucial data. Well without the proper rights it really becomes more Complicated. But if you have db backup n restore right then you can perform it .

Or else, One way that I would recommend though is to drop all of your constraints and indexes and then again add them once the data has been imported or exported.

Not an exact answer but it will process fast.

share|improve this answer
    
Thanks, but I specificly said i dont want to script out a drop/create constraint script. –  user11512 May 26 '13 at 14:27
add comment

In the import wizard, you can delete the rows first and if you have identity fields, then you can enable identity insert on as below

enter image description here

If you want to disable check constraint, then when the wizard asks you to save the package, save it and then edit the connection manager as below :

enter image description here

Note: You cannot TRUNCATE the table when there are Foreign Keys defined.

share|improve this answer
    
I am running off the web version of SQL 2012 so saving a package is not an option. I was able to import the data with no issues using the commands I posted that I got from SQLTeam.com –  user11512 May 26 '13 at 16:25
    
@MatthewVerstraete Please mention that in your question as others will save some time –  Kin May 26 '13 at 19:31
    
I did not relize the limitations until I tired your option. I got to the very last step and noticed the message saying I had to upgrade to save the package. Sorry I did not know about it before hand. Looking into your solution it would work if I could do it though. I would upvote it but i dont have the rep for it yet. –  user11512 May 26 '13 at 21:04
    
@MatthewVerstraete thanks for getting back. When you have enough points you can revisit this and upvote it. Let me know if you have any further issues or questions –  Kin May 27 '13 at 15:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.