How to Auto increment column with merge table not matched case in SQL SERVER?

Question

I'm going to insert rows in target table using MERGE tables in SQL Server 2014. I want to auto incremental ID column in target table.

CREATE TABLE #targetTable(id int,name varchar(50))
CREATE TABLE #sourceTable(id int,name varchar(50))

INSERT INTO #sourceTable values(1,'John');
INSERT INTO #sourceTable values(1,'Albrt');
INSERT INTO #sourceTable values(1,'Roy');

MERGE #targetTable AS [target]
USING #sourceTable AS [source]
ON [target].id = [source].id
WHEN NOT MATCHED THEN
INSERT (id, Name)
VALUES ((select isnull(max(id),1) + 1 from #sourceTable), source.Name);

select * from #targetTable as T
drop table #targetTable
drop table #sourceTable

I had tried to do like select isnull(max(id),1) + 1 from #sourceTable but its gives same ids to all columns. This one will return below output

2   John
2   Albrt
2   Roy

output needed like

2   John
3   Albrt
4   Roy

What changes i have to do for my expected output?

Answer 1

CREATE TABLE #targetTable(id int, name varchar(50))
CREATE TABLE #sourceTable(id int,name varchar(50))

INSERT INTO #sourceTable values(1,'John');
INSERT INTO #sourceTable values(1,'Albrt');
INSERT INTO #sourceTable values(1,'Roy');

MERGE #targetTable AS [target]
USING   
    (
        select  id, 
                name, 
                ROW_NUMBER() OVER(ORDER BY id) as rnk 
        from    #sourceTable
    ) AS [source]
ON [target].id = [source].id
WHEN NOT MATCHED THEN
INSERT (id, Name)
VALUES (rnk, source.Name);

select * from #targetTable as T
drop table #targetTable
drop table #sourceTable