update t1 set t1.item1 = t2.item1, t1.item2 = t2.item2 ... from t1, t2 where t1.id = t2.id and (t1.item1 <> t2.item1 or t1.item2 <> t2.item2 ......)
set nocount on create table #updateTable(tid varchar(15),HowUpdate varchar(15)) insert into #updateTable select tid ,'Del' from t1 where tid not in(select tid from t2)insert into #updateTable select tid ,'Update' from t1 where tid in (select tid from t2)insert into #updateTable select tid ,'Insert' from t2 where tid not in (select tid from t1)delete t1 where tid in (select tid from #updateTable where HowUpdate = 'Del')update t1 set field1=B.field1,field2=B.filed2 from t2 B where tid =B.tidinsert into t1 select * from t2 where tid in (select tid from #updateTable where howUpdate='Insert')select * from #updateTabledrop table #updateTable
好像最後的T1 的數據就是T2原來的所有數據阿delete t1 insert into t1 select * from t2
from t1, t2
where t1.id = t2.id
and (t1.item1 <> t2.item1
or t1.item2 <> t2.item2
......)
create table #updateTable(tid varchar(15),HowUpdate varchar(15))
insert into #updateTable
select tid ,'Del' from t1 where tid not in(select tid from t2)insert into #updateTable
select tid ,'Update' from t1 where tid in (select tid from t2)insert into #updateTable
select tid ,'Insert' from t2 where tid not in (select tid from t1)delete t1 where tid in (select tid from #updateTable where HowUpdate = 'Del')update t1 set field1=B.field1,field2=B.filed2
from t2 B
where tid =B.tidinsert into t1
select * from t2
where tid in
(select tid from #updateTable where howUpdate='Insert')select * from #updateTabledrop table #updateTable
insert into t1
select * from t2