SQL Server Merge query - duplicates?

Question

I have 2 table variables

• First table has 1, 3, 5
• Second table has 2, 4, 5

However when I run the following query :

 DECLARE @t1 TABLE (a int)
 DECLARE @t2 TABLE (b int)

 INSERT INTO @t1 (a) VALUES ( 1 ),(3),(5)  
 INSERT INTO @t2 (b) VALUES ( 2 ),(4),(5)

;WITH Source AS (
    SELECT * from  @t1
 )
 MERGE INTO @t2
 USING Source ON 1 = 0

 WHEN NOT MATCHED THEN
     INSERT (b) VALUES  (a);

 SELECT * FROM @t2 

I get non-fully merged results :

Instead of getting 2, 4, 5, 1, 3, I get 2, 4, 5, 1, 3, 5

Question

Why do I see a double 5 ? It is a merge query and 5 is matched to the other 5 in the second table.

Answer 1

You are matching on 1=0 which will always fire the insert. You should use On Source.a = @t2.b

Answer 2

Because your on clause is 1 = 0 nothing matches, so all rows are inserted.

Changing your on clause to a = b will yield your expected results of 2,4,5,1,3.

rextester for on a = b: http://rextester.com/OPLL86727

It might be helpful to be more explicit with aliasing your source and target:

declare @t1 table (a int)
declare @t2 table (b int)
insert into @t1 (a) values ( 1 ),(3),(5)  
insert into @t2 (b) values ( 2 ),(4),(5)

;with source as (
  select * from  @t1
)
  merge into @t2 as target
  using source
    on source.a = target.b
    when not matched then
      insert (b) values  (a);

select * 
from @t2;