I have a TblSource table (from a linked db) that I need to insert into an equivalent TblDest table with ids so I need to check for duplicate ids during insert. TblDest is a child to tblMaster so during insert I also need to check if a parent row exists in tblMaster. I have tried below code at the end but it seems the two EXISTS/ NOT EXISTS clauses don’t work as needed. I get partial success if I change the order of the clauses, not sure why.
Currently I am getting below error;
Violation of PRIMARY KEY constraint 'PK_TblDest'. Cannot insert duplicate key in object 'dbo.TblDest'. The duplicate key value is (8).
How can I achieve a successful inset? Thanks.
SET IDENTITY_INSERT tbldest ON go INSERT INTO tbldest(id,masterid) SELECT tblsource.id,tblsource.[master id] FROM [LinkedDB]...tblsource WHERE ( NOT EXISTS (SELECT tbldest.id FROM tbldest INNER JOIN [LinkedDB]...tblsource ON tbldest.id = tblsource.id) ) AND ( EXISTS (SELECT tblmaster.id FROM tblmaster INNER JOIN [LinkedDB]...tblsource ON tblmaster.id = tblsource.[master id]) ) go SET IDENTITY_INSERT tbldest OFF go