I have a stored procedure with condition but merge query is inserting duplicates despite condition. What is wrong?
CREATE PROCEDURE [dbo].[AddDuplicate] @AIDNew AS INT, @AID AS INT AS BEGIN DECLARE @MapEvaris TABLE(OldEID int, NewEID int); MERGE INTO tblEvaris USING (SELECT EID,Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc FROM tblEvaris WHERE tblEvaris.AID = @AID) AS Sel ON 1 = 0 WHEN NOT MATCHED BY TARGET THEN INSERT (Componenta,FactorRisc,FormaManifestare,Gravitate,ClsGravitate,Probabilitate,ClsProbabilitate,Risc,AID) VALUES (Sel.Componenta,Sel.FactorRisc,Sel.FormaManifestare,Sel.Gravitate,Sel.ClsGravitate,Sel.Probabilitate,Sel.ClsProbabilitate,Sel.Risc,@AIDNew) OUTPUT Sel.EID AS OldEID, INSERTED.EID AS NewEID INTO @MapEvaris(OldEID, NewEID); INSERT INTO tblMasura (TipMasura, Masura, GravitateF, ClsGravitateF, ProbabilitateF, ClsProbabilitateF, RiscF, EID) SELECT m.TipMasura, m.Masura, m.GravitateF, m.ClsGravitateF, m.ProbabilitateF, m.ClsProbabilitateF, m.RiscF, me.NewEID FROM tblMasura m JOIN tblEvaris e ON m.EID = e.EID JOIN @MapEvaris AS me ON me.OldEID = e.EID WHERE e.AID = @AID AND NOT EXISTS (SELECT 1 FROM tblMasura m JOIN @MapEvaris AS me ON me.OldEID = m.EID WHERE (m.EID = me.NewEID)) END