Insert Statement to insert Data from one table to another

Question

I have the following SQL Code to Insert Data From One table to Another by comparing the Data in two table. The PK in the destination table are [bomItem], [bomRev], [bomEntry].

I want to Insert if ItemID in source table = bomItem field in Dest. table AND [rev](field in Source tbl)NOT=[bomRev](in destination tbl).

When I tried to run the script i get the following error

Msg 547, Level 16, State 0, Line 46 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_MIBOMD_MIBOMH". The conflict occurred in database "MITESTCO", table "dbo.MIBOMH". The statement has been terminated.

    USE [MITESTCO];
    GO

   INSERT INTO [MIBOMD] 
    ([bomItem], [bomRev], [bomEntry], [partId], [qty],[lead])
 SELECT [ItemID], [rev], [bomEntry], [partid], [qty],[lead]
 FROM [assy] 
 WHERE  [rev] IN (SELECT [bomRev] FROM [MIBOMH])
        AND [ItemID] IN (SELECT [bomItem] FROM [MIBOMH])
        AND [ItemID] IN (SELECT [ItemID] FROM [MIITEM])
        AND [partid] IN (SELECT [ItemID] FROM [MIITEM]);

Error has nothing directly to do with query but design structure of your database tables. As error says a foreign key constraint is being violated which by its name may involve the appending table, `dbo.MIBOMD` with `dbo.MIBOMH`. Maybe you have one-to-many table relationship between these two (parent/child) and are appending records in child, leaving connecting parent ID NULL. — Parfait, May 01 '16 at 00:42
@Parfait Thanks! Yes i have one-to-Many relationship and i have the same data/record in the parent and Child the only d/c is the `rev` field. The Primary Keys in the parent tables are `bomRev` and `bomItem`. How can i resolve this you said Apeending But i dont know how that work Can you explain me more? — Kin, May 01 '16 at 01:00
Check your constraints: http://stackoverflow.com/questions/2499332/how-to-check-if-a-constraint-exists-in-sql-server — Parfait, May 01 '16 at 01:09
You could look into turning off the constraints temporarily, do your inserts and turn it back on. — freakyhat, May 01 '16 at 05:17

score 0 · Answer 1 · answered May 04 '16 at 08:11

To disable all constraints in the database -

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Insert data

To switch them back on -

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Insert Statement to insert Data from one table to another

1 Answers1