SCD TYPE 2 LOGIC

In addition to Vaibhah answer, Maybe you should put all THE restrictions in the "on" Clause. This way there Will only be an insert when All 4 fields in THE on clause do not match.

Only when matched normally you do an update. My advice would-be be if THE id is matched always do an update of THE remainng fields except id and an insert when THE id is not in THE target table.

SEe link for an example:

http://ift.tt/1vMP7Vh

CREATE TABLE #EMP1(
        [id] [int] IDENTITY(1,1) NOT NULL,
        [fname] [varchar](25) NULL,
        [lname] [varchar](25) NULL
)

CREATE TABLE #EMP2(
        [id] [int] IDENTITY(1,1) NOT NULL,
        [fname] [varchar](25) NULL,
        [lname] [varchar](25) NULL
)

INSERT INTO #EMP1 SELECT 'J','Smith'
INSERT INTO #EMP2 SELECT 'John','Smithson'

MERGE INTO #EMP1 AS TGT
USING #EMP2 AS SRC
        ON TGT.ID=SRC.ID
WHEN MATCHED THEN
        UPDATE SET
        TGT.FNAME=SRC.FNAME,
        TGT.LNAME=SRC.LNAME
WHEN NOT MATCHED THEN
        INSERT (FNAME,LNAME)
        VALUES(SRC.FNAME,SRC.LNAME);

Regards,

Reshma

---

Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered