--id不存在,则添加。 insert into tb1(id,name,yuwen,shuxue) select id,name,yuwen,shuxue from tb2 where id not in( select id from tb1 ) --如果id相同,则更新所有字段 update a set name=b.name from table1 a inner join table2 b on a.id=b.ic
if OBJECT_ID('tb1')is not null drop table tb1 if object_id('tb2') is not null drop table tb2 go create table tb1 (id int,name nvarchar(10),yuwen int,shuxue int) insert into tb1 select 1,'a',11,20 union all select 2,'b',10,20 union all select 3,'c',10,20 create table tb2 (id int,name nvarchar(10),yuwen int,shuxue int) insert into tb2 select 1,'x',20,20 union all select 2,'b',10,10 union all select 3,'c',10,20 union all select 4,'y',30,40 go insert into tb1 select * from tb2 a where not exists (select * from tb1 where a.id=id) update a set a.name=b.name ,a.yuwen=b.yuwen,a.shuxue=b.shuxue from tb1 a join tb2 b on a.id=B.id select * from tb1 select * from tb2(4 行受影响) id name yuwen shuxue ----------- ---------- ----------- ----------- 1 x 20 20 2 b 10 10 3 c 10 20 4 y 30 40(4 行受影响)id name yuwen shuxue ----------- ---------- ----------- ----------- 1 x 20 20 2 b 10 10 3 c 10 20 4 y 30 40(4 行受影响)
--id不存在,则添加。 insert into tb1(id,name,yuwen,shuxue) select id,name,yuwen,shuxue from tb2 where id not in( select id from tb1 ) --如果id相同,则更新所有字段 update a set name=b.name from table1 a inner join table2 b on a.id=b.ic
insert into tb1(id,name,yuwen,shuxue) select id,name,yuwen,shuxue from tb2 where id not in(
select id from tb1
)
--如果id相同,则更新所有字段
update a set name=b.name
from table1 a inner join table2 b on a.id=b.ic
if OBJECT_ID('tb1')is not null
drop table tb1
if object_id('tb2') is not null
drop table tb2
go
create table tb1 (id int,name nvarchar(10),yuwen int,shuxue int)
insert into tb1 select 1,'a',11,20
union all select 2,'b',10,20
union all select 3,'c',10,20
create table tb2 (id int,name nvarchar(10),yuwen int,shuxue int)
insert into tb2 select 1,'x',20,20
union all select 2,'b',10,10
union all select 3,'c',10,20
union all select 4,'y',30,40
go
insert into tb1 select * from tb2 a where not exists (select * from tb1 where a.id=id)
update a set a.name=b.name ,a.yuwen=b.yuwen,a.shuxue=b.shuxue from tb1 a join tb2 b on a.id=B.id
select * from tb1
select * from tb2(4 行受影响)
id name yuwen shuxue
----------- ---------- ----------- -----------
1 x 20 20
2 b 10 10
3 c 10 20
4 y 30 40(4 行受影响)id name yuwen shuxue
----------- ---------- ----------- -----------
1 x 20 20
2 b 10 10
3 c 10 20
4 y 30 40(4 行受影响)
insert into tb1(id,name,yuwen,shuxue) select id,name,yuwen,shuxue from tb2 where id not in(
select id from tb1
)
--如果id相同,则更新所有字段
update a set name=b.name
from table1 a inner join table2 b on a.id=b.ic