IDENTICAL ROWS MANIPULATION THROUGH MERGE STATEMENT

There is a question related to Merge statement which oftentimes asked in interviews :
If you are inserting and updating target table from the source table and in source there are identical rows, then what would be the out put?

Generally developer replies only one row will get inserted out of identical rows, which is wrong,

The correct answer is both identical rows will get inserted.

Below is the demonstration of this :

--------------------------------------------

--IDENTICAL ROWS MANIPULATION THROUGH MERGE

--------------------------------------------

CREATE TABLE STUDENTSOURCE

(

      ID INT,

      NAME VARCHAR(20)

)

CREATE TABLE STUDENTTARGET

(

      ID INT,

      NAME VARCHAR(20)

)

-- In the below source table there are two identical rows

-----------------------------------------------------------

INSERT INTO STUDENTSOURCE VALUES (1,'MIKE')

INSERT INTO STUDENTSOURCE VALUES (2,'SARA')

INSERT INTO STUDENTSOURCE VALUES (2,'SARA')

-- Inserted the rows into Target Table

----------------------------------------------

INSERT INTO STUDENTTARGET VALUES (1,'MIKE M')

INSERT INTO STUDENTTARGET VALUES (3,'JOHN')

-- Apply Merge statement

----------------------------

MERGE STUDENTTARGET AS T

USING STUDENTSOURCE AS S

ON T.ID=S.ID

WHEN MATCHED THEN

UPDATE SET T.NAME=S.NAME

WHEN NOT MATCHED BY TARGET THEN

INSERT (ID,NAME) VALUES(S.ID,S.NAME)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

SELECT * FROM STUDENTSOURCE

Output: Source Table output

SELECT * FROM STUDENTTARGET

Output: Target Table output

Conclusion :  Here two identical rows inserted into Target table because Merge statement performs Bulk insert instead of single insert.By the join condition 2 identical rows get matched so both identical rows inserted through bulk insert operation in Target table.