如何将B表的内容复制到A表,不存在的插入,存在的更新
A表
ID Year Value
AAA 2005 100
BBB 2006 200
CCC 2006 500
B表(临时表)
ID Year Value
CCC 2006 1000
DDD 2007 800
ID 和Year是主键
更新后A表
A表
ID Year Value
AAA 2005 100
BBB 2006 200
CCC 2006 1000
DDD 2007 800
用语句怎么实现效率高呢?
我现在想的是先Update一次,再insert一次
[解决办法]
update A set A.[Year]=B.[Year], A.Value=B.Value
from A, B
where A.ID=B.ID
insert A
select * from B as tmp
where not exists(select 1 from A where ID=tmp.ID)
[解决办法]
create table A(ID varchar(10), [Year] int, Value int)
insert A select 'AAA ', 2005, 100
union all select 'BBB ', 2006, 200
union all select 'CCC ', 2006, 500
go
create table B(ID varchar(10), [Year] int, Value int)
insert B select 'CCC ', 2006, 1000
union all select 'DDD ', 2007, 800
go
update A set A.[Year]=B.[Year], A.Value=B.Value
from A, B
where A.ID=B.ID
go
insert A
select * from B as tmp
where not exists(select 1 from A where ID=tmp.ID)
go
select * from A
--result
ID Year Value
---------- ----------- -----------
AAA 2005 100
BBB 2006 200
CCC 2006 1000
DDD 2007 800
(4 row(s) affected)
[解决办法]
update A set A.[Year]=B.[Year], A.Value=B.Value
from A inner join B on A.ID=B.ID
insert into A(ID,[Year],[Value])
select ID,[Year],[Value]
from B
where checksum(*) not in (select checksum(*) from A)
