select a_id as b_id,a_name as b_name,a_other as b_else1,null as b.else2 from test1 unionn all select b_id,b_name,b_else1,b_else2 where b_id not in (select a_id from test1) delete test1
insert test2 (b_id, b_name,b_else1) values (select a_id, a_name,a_other from test1 where a_id not in (select b_id from test1)) update test2 set b_name=a_name, b_else1=a_other from test1 where test1.a_id=test2.b_id,b_else2='' where test1.a_id=test2.b_id trunact table test1
忘了你是要插入: select a_id as b_id,a_name as b_name,a_other as b_else1,null as b.else2 into #tmp from test1 unionn all select b_id,b_name,b_else1,b_else2 where b_id not in (select a_id from test1) delete test2 insert into test2 select * from #tmp delete test1
unionn all select b_id,b_name,b_else1,b_else2 where b_id not in (select a_id from test1)
delete test1
update test2 set b_name=a_name, b_else1=a_other from test1 where test1.a_id=test2.b_id,b_else2='' where test1.a_id=test2.b_id
trunact table test1
select a_id as b_id,a_name as b_name,a_other as b_else1,null as b.else2 into #tmp from test1
unionn all select b_id,b_name,b_else1,b_else2 where b_id not in (select a_id from test1)
delete test2
insert into test2 select * from #tmp
delete test1