Procedure is inserting duplicates despite condition

Marius Vasile

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted answer

***: Assuming EID is the unique identifier to avoid duplicates

MERGE INTO tblEvaris AS TargetTable
USING (
    SELECT EID, Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc
    FROM tblEvaris 
    WHERE tblEvaris.AID = @AID
) AS SourceTable
ON TargetTable.EID = SourceTable.EID -- ***
WHEN NOT MATCHED THEN
    INSERT (Componenta, FactorRisc, FormaManifestare, Gravitate, ClsGravitate, Probabilitate, ClsProbabilitate, Risc, AID)
    VALUES (SourceTable.Componenta, SourceTable.FactorRisc, SourceTable.FormaManifestare, SourceTable.Gravitate, SourceTable.ClsGravitate, 
		    SourceTable.Probabilitate, SourceTable.ClsProbabilitate, SourceTable.Risc, @AIDNew)
OUTPUT SourceTable.EID AS OldEID, INSERTED.EID AS NewEID INTO @MapEvaris(OldEID, NewEID);

Marius Vasile2y ago
Ok, I got it, condition should be ON TargetTable.AID = SourceTable.AID, check the rows to see if new data was added for the new AID

Thank you Amit, idea was good, update your code to reflect the correct answer.
+1
Marius Vasile2y ago
Amit, I tried the solution proposed but now there is no insert, no data is addedto table
+1
Marius Vasile2y ago
Well, I can't remove the condition or at least I have to replece it with something else, but with what? I don't know
+1
Amit Mohanty2y ago
ON 1 = 0 condition means that there is no match condition. This would result in an insert for every row in the Sel subquery without considering any conditions from the target table.
+1
Marius Vasile2y ago
1=0 ensure WHEN NOT MATCHED BY TARGET is always executed. Shouldn't be like that?
+1
Amit Mohanty2y ago
The MERGE statement appears to be always inserting rows into tblEvaris, as the condition ON 1 = 0 will never be satisfied (1 = 0 is always false). May be thats the reason the INSERT statement inside the MERGE block will execute every time the procedure runs.
+1