insert into TB select * from tA where 主键 not in (select 主键 from TB)
delete from tb a where exists (select * from ta b where a.主键=b.主键) insert tb select * from ta
update TB set 字段1=TA.字段1 ....... from TB where exists(select 1 from TA where 主键=TB.主键)insert TB select * from TA where not exists(select 1 from TB where 主键=TA.主键)
vivianfdlpw():你的第一条的Update语句好像不正确哦,呵呵,我是这样写的,还是报错~~ UPDATE CTArchives SET ID = #tmp_CTArchives.ID FROM CTArchives WHERE EXISTS (SELECT 1 FROM #tmp_CTArchives WHERE ID = CTArchives.ID) 报的错误是:列前缀 '#tmp_CTArchives' 与查询中所用的表名或别名不匹配。
UPDATE CTArchives SET ID = #tmp_CTArchives.ID FROM CTArchives,#tmp_CTArchives where ... 及联更新,From后面要写两个表
漏掉了:update TB set 字段1=TA.字段1 ....... from TB join TA on TA.主键=TB.主键 where exists(select 1 from TA where 主键=TB.主键)
update TB set 字段1=TA.字段1 ....... from TB join TA on TA.主键=TB.主键
insert tb
select * from ta
set 字段1=TA.字段1
.......
from TB
where exists(select 1 from TA where 主键=TB.主键)insert TB
select * from TA
where not exists(select 1 from TB where 主键=TA.主键)
SET ID = #tmp_CTArchives.ID
FROM CTArchives
WHERE EXISTS (SELECT 1 FROM #tmp_CTArchives WHERE ID = CTArchives.ID)
报的错误是:列前缀 '#tmp_CTArchives' 与查询中所用的表名或别名不匹配。
SET ID = #tmp_CTArchives.ID
FROM CTArchives,#tmp_CTArchives
where ...
及联更新,From后面要写两个表
set 字段1=TA.字段1
.......
from TB
join TA on TA.主键=TB.主键
where exists(select 1 from TA where 主键=TB.主键)
set 字段1=TA.字段1
.......
from TB
join TA on TA.主键=TB.主键