IF OBJECT_ID('stage.sp_MrgMukodesihely') IS NOT NULL BEGIN
  DROP PROCEDURE stage.sp_MrgMukodesihely
END
GO

CREATE PROCEDURE stage.sp_MrgMukodesihely
AS
BEGIN
  MERGE stage.T_MUKODESIHELY_STAGE AS trg
    USING (
      SELECT
         m.C_INTEZMENYID AS C_INTEZMENYID 
        ,t.C_NEV AS C_TANEVNEV
        ,m.ID AS C_MUKODESIHELYID 
        ,m.C_NEV
        ,m.C_EMAILCIM
        ,m.C_FAX
        ,m.C_IRANYITOSZAM
        ,m.C_TELEFONSZAM
        ,m.C_VAROS
        ,m.C_VEZETONEVE
        ,m.C_TAGINTEZMENY
        ,m.C_TAGINTEZMENYIKOD
        ,m.C_SZEKHELY
      FROM dbo.T_MUKODESIHELY m
        INNER JOIN dbo.T_TANEV t ON t.ID = m.C_TANEVID
      WHERE m.TOROLT = 'F'  
    ) src ON (trg.C_INTEZMENYID = src.C_INTEZMENYID AND trg.C_MUKODESIHELYID = src.C_MUKODESIHELYID) 
    WHEN MATCHED AND 
         (trg.C_NEV <> src.C_NEV OR (trg.C_NEV IS NULL AND src.C_NEV IS NOT NULL) OR (trg.C_NEV IS NOT NULL AND src.C_NEV IS NULL))
      OR (trg.C_EMAILCIM <> src.C_EMAILCIM OR (trg.C_EMAILCIM IS NULL AND src.C_EMAILCIM IS NOT NULL) OR (trg.C_EMAILCIM IS NOT NULL AND src.C_EMAILCIM IS NULL))
      OR (trg.C_FAX <> src.C_FAX OR (trg.C_FAX IS NULL AND src.C_FAX IS NOT NULL) OR (trg.C_FAX IS NOT NULL AND src.C_FAX IS NULL))
      OR (trg.C_IRANYITOSZAM <> src.C_IRANYITOSZAM OR (trg.C_IRANYITOSZAM IS NULL AND src.C_IRANYITOSZAM IS NOT NULL) OR (trg.C_IRANYITOSZAM IS NOT NULL AND src.C_IRANYITOSZAM IS NULL))
      OR (trg.C_TELEFONSZAM <> src.C_TELEFONSZAM OR (trg.C_TELEFONSZAM IS NULL AND src.C_TELEFONSZAM IS NOT NULL) OR (trg.C_TELEFONSZAM IS NOT NULL AND src.C_TELEFONSZAM IS NULL))
      OR (trg.C_VAROS <> src.C_VAROS OR (trg.C_VAROS IS NULL AND src.C_VAROS IS NOT NULL) OR (trg.C_VAROS IS NOT NULL AND src.C_VAROS IS NULL))
      OR (trg.C_VEZETONEVE <> src.C_VEZETONEVE OR (trg.C_VEZETONEVE IS NULL AND src.C_VEZETONEVE IS NOT NULL) OR (trg.C_VEZETONEVE IS NOT NULL AND src.C_VEZETONEVE IS NULL))
      OR (trg.C_TAGINTEZMENY <> src.C_TAGINTEZMENY OR (trg.C_TAGINTEZMENY IS NULL AND src.C_TAGINTEZMENY IS NOT NULL) OR (trg.C_TAGINTEZMENY IS NOT NULL AND src.C_TAGINTEZMENY IS NULL))
      OR (trg.C_TAGINTEZMENYIKOD <> src.C_TAGINTEZMENYIKOD OR (trg.C_TAGINTEZMENYIKOD IS NULL AND src.C_TAGINTEZMENYIKOD IS NOT NULL) OR (trg.C_TAGINTEZMENYIKOD IS NOT NULL AND src.C_TAGINTEZMENYIKOD IS NULL))
      OR (trg.C_SZEKHELY <> src.C_SZEKHELY OR (trg.C_SZEKHELY IS NULL AND src.C_SZEKHELY IS NOT NULL) OR (trg.C_SZEKHELY IS NOT NULL AND src.C_SZEKHELY IS NULL))
    THEN
      UPDATE SET
         trg.C_NEV = src.C_NEV
        ,trg.C_EMAILCIM = src.C_EMAILCIM
        ,trg.C_FAX = src.C_FAX
        ,trg.C_IRANYITOSZAM = src.C_IRANYITOSZAM
        ,trg.C_TELEFONSZAM = src.C_TELEFONSZAM
        ,trg.C_VAROS = src.C_VAROS
        ,trg.C_VEZETONEVE = src.C_VEZETONEVE
        ,trg.C_TAGINTEZMENY = src.C_TAGINTEZMENY
        ,trg.C_TAGINTEZMENYIKOD = src.C_TAGINTEZMENYIKOD
        ,trg.C_SZEKHELY = src.C_SZEKHELY
        ,trg.LASTCHANGED = GETDATE()
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (
         C_INTEZMENYID 
        ,C_TANEVNEV
        ,C_MUKODESIHELYID 
        ,C_NEV
        ,C_EMAILCIM
        ,C_FAX
        ,C_IRANYITOSZAM
        ,C_TELEFONSZAM
        ,C_VAROS
        ,C_VEZETONEVE
        ,C_TAGINTEZMENY
        ,C_TAGINTEZMENYIKOD
        ,C_SZEKHELY
        ,TOROLT
        ,LASTCHANGED
        ,CREATED
      ) VALUES (
         src.C_INTEZMENYID 
        ,src.C_TANEVNEV
        ,src.C_MUKODESIHELYID 
        ,src.C_NEV
        ,src.C_EMAILCIM
        ,src.C_FAX
        ,src.C_IRANYITOSZAM
        ,src.C_TELEFONSZAM
        ,src.C_VAROS
        ,src.C_VEZETONEVE
        ,src.C_TAGINTEZMENY
        ,src.C_TAGINTEZMENYIKOD
        ,src.C_SZEKHELY
        ,'F'
        ,GETDATE()
        ,GETDATE()
      )
    WHEN NOT MATCHED BY SOURCE AND trg.TOROLT = 'F' THEN
      UPDATE SET 
        trg.LASTCHANGED = GETDATE(), 
        trg.TOROLT = 'T'
  ;
END
GO