insert a(t1) select T1 from b where t1 not in(select b.t1 from b,a where a.t1=b.t1)
如果你是想把b表中不与a重复的数据加到a表中 select * from t2 where t2.id not in (select id from t1)
FT,一个SQL能实现吗??又要插入,又要更改。
create table #temp(t1 char(10)) declare @t1 char(10) while exists (select t1 from b where t1 not in (select t1 from #temp ) ) begin select top 1 @t1 = t1 from b where t1 not in (select t1 from #temp ) insert into #temp select @t1 if exists (select * from a where t1 = @t1) insert into a select cast(convert(int,max(t1))+1 as char(10)) as t1 from a where left(t1,8) = left(@t1,8) group by left(t1,8) else insert into a select @t1 end select t1 from a order by t1
select t1 from b
select distinct id from b where id not in (select distinct id from a)
insert into a
select * from b
where not exists( select t1 from a where a.t1=b.T1)
select * from b
各位要将B表加入A后,B.T1数据是有变化的,表A.t1
2002020101
2002020102
2002020103 <----------是B表中2002020101 有变化
2002020104 <----------是B表中2002020102 有变化
2002030101
2002030102
2002030103
2002030104 <----------是B表中2002030101 有变化
2002040101 <----------是B表中2002040101 没有变化
2002040102 <----------是B表中2002040102 没有变化
2002050101 <----------是B表中2002050101 没有变化
select * from t2 where t2.id not in (select id from t1)
declare @t1 char(10)
while exists (select t1 from b where t1 not in (select t1 from #temp ) )
begin
select top 1 @t1 = t1 from b where t1 not in (select t1 from #temp )
insert into #temp select @t1
if exists (select * from a where t1 = @t1)
insert into a
select cast(convert(int,max(t1))+1 as char(10)) as t1 from a where left(t1,8) = left(@t1,8) group by left(t1,8)
else
insert into a select @t1
end
select t1 from a order by t1