merge statement - upsert - performing unique test in source table as well

Question

I need some help with SQL Server merge statement. I am using version 2008.

I have two tables table1 and table2 with 3 column in each table: name, age, lastname.

I want to do little variant of Upsert from table2 to table1. If record exists in table 1, ignore. If doesn't exist then insert.

I know following would work -

merge into [test].[dbo].[table1] a
using [test].[dbo].[table2] b
on a.name = b.name and a.lastname = b.lastname
when not matched then
insert (name, age, lastname) values (b.name, b.age, b.lastname)

I would like to know if I could do something like this? Currently following doesn't work:

merge into [test].[dbo].[table1] a
using [test].[dbo].[table2] b
on a.name = b.name and a.lastname = b.lastname
when not matched then
insert (select name, max(age), lastname from b group by name, lastname)

Basically I want to insert only 'unique records' from table 2 to table 1. Unique means name and lastname should be same.

Thanks.

Answer 1

Its not really an UPSERT operation its a simple insert and I would do something like this....

insert into [test].[dbo].[table1](name, age, lastname)
SELECT b.name, MAX(b.age) Age, b.lastname
FROM [test].[dbo].[table2] b
WHERE NOT EXISTS (SELECT 1
                  FROM [test].[dbo].[table1]
                  WHERE name = b.name 
                   and lastname = b.lastname)
GROUP BY b.name, b.lastname

UPSERT would be if you updated records if they already existed.

Answer 2

For just insert you don't really Merge. An insert alone should be enough. But heres a way to do it

merge into [test].[dbo].[table1] a
using (
    select  
        name,
        lastname,
        max(age) age
    from [test].[dbo].[table2] 
    group by
        name,
        lastname
) b on 
    a.name = b.name and 
    a.lastname = b.lastname
when not matched 
then
insert (
    name,
    lastname,
    age
) 
VALUES (
    b.name,
    b.lastname,
    b.age
);