--> 测试数据：[Tbl1]
go
if object_id('[Tbl1]') is not null 
drop table [Tbl1]
go
create table [Tbl1](
[id] int,
[Name] varchar(5)
)
go
insert [Tbl1]
select 1,'Name1' union all
select 2,'Name2'
go
create table tbl2(
[id] int,
[Name] varchar(5),
Test varchar(4)
)


insert tbl2
select *,Test='test' from Tbl1

select * from tbl2

/*
id    Name    Test
1    Name1    test
2    Name2    test
*/

------解决方案--------------------
如果存在对应的ID，则UPDATE
update tbl2 set test = 'test' from tbl2 t2 , tbl1 t1 where t2.id = t1.id

如果对应ID不存在，则需要insert
insert into tbl2 select id , name , 'test' from tbl1 t1 where not exists(select 1 from tbl2 t2 where t2.id = t1.id)