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.
| Author |
Topic |
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
 Posted - 2013-05-25 : 19:55:21
|
| 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 way to deal with this without having to write a drop constraint/add constrat script?-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-05-25 : 20:20:54
|
| You can disable foreign key constraint and enable it after insert operation.Follow this link:http://msdn.microsoft.com/en-us/library/10cetyt6(v=vs.80).aspx |
 |
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2013-05-25 : 21:20:09
|
quote:
Originally posted by MuMu88
You can disable foreign key constraint and enable it after insert operation.Follow this link:http://msdn.microsoft.com/en-us/library/10cetyt6(v=vs.80).aspx
That is even more work then scripting a drop/add constraint file plus it requires Visual Studio. I am looking for an efficent SQL Server solution.-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-26 : 00:20:50
|
Either you need to serialize the data imports and have them in the proper order, or...You can easily disable all constraints like this:EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' Then import your data, before enabling all constraints:EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all' |
|
|
|
Eagle_f90
Constraint Violating Yak Guru
424 Posts |
Posted - 2013-05-26 : 10:37:28
|
| That worked thanks perfectly!-- If I get used to envying others...Those things about my self I pride will slowly fade away.-Stellvia |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-26 : 11:15:51
|
Welcome.  Glad it worked for you. |
|
|
|
zkwaywow
Starting Member
3 Posts |
Posted - 2013-06-04 : 04:46:15
|
| That's great,I agree with your thought.__________________Living without an aim is like sailing without a compass.unspammed |
|
|
|
|
|
|
Dealing with FK in DTS copying?