Marius Vasile

Marius Vasile

  • 592
  • 1.9k
  • 146k

Procedure is inserting duplicates despite condition

Nov 27 2023 1:55 PM

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

 


Answers (7)