[mamcx]

La manera mas correcta usando SQL moderno es usando MERGE:

https://docs.microsoft.com/es-mx/sql...l-server-ver15

Código SQL [-]
ALTER PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS
BEGIN  
    SET NOCOUNT ON;  
  
    MERGE Production.UnitMeasure AS tgt  
    USING (SELECT @UnitMeasureCode, @Name) as src (UnitMeasureCode, Name)  
    ON (tgt.UnitMeasureCode = src.UnitMeasureCode)  
    WHEN MATCHED THEN
        UPDATE SET Name = src.Name  
    WHEN NOT MATCHED THEN  
        INSERT (UnitMeasureCode, Name)  
        VALUES (src.UnitMeasureCode, src.Name)  
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;  
END;

Es muy importante entender que este método es mas confiable y veloz que la forma antigua, que solo se usa si la version del motor es VIEJA.

Este articulo explica bien los problemas de hacer un "upset/merge" ad-hoc:

https://michaeljswart.com/2017/07/sq...-antipatterns/