procedure TForm1.Button3Click(Sender: TObject); begin adoquery1.Close; adoquery1.SQL.Clear; adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b group by sfzh1 having count(*)>1)'); adoquery1.Open; end;
procedure TForm1.Button4Click(Sender: TObject); begin adoquery1.Close; adoquery1.SQL.Clear; adoquery1.SQL.Add('alter table b add idMytest int identity(1,1)'); adoquery1.ExecSQL; adoquery1.Close; adoquery1.SQL.Clear; adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b where b.idMytest<idMytest '+ 'group by sfzh1 having count(*)>1)'); adoquery1.ExecSQL; adoquery1.Close; adoquery1.SQL.Clear; adoquery1.SQL.Add('alter table b drop column idMytest'); adoquery1.ExecSQL;end;
如果两个表本来就没有重复的记录,担心合并到一起后会有重复记录
insert into 表1 select * from 表2 where 某关键字 not in (select 某关键字 from 表1)
insert into TableB(.,.,.,.,.) selct .,.,.,.,. from TableA where ID not in (select ID from TableB)
//where后面的条件自己加上
然后从一库中取数据INSERT 到另外的库中去,INSERT的时候判断是不是有重复记录
Insert Into 表1
SELECT * FROM [;database=C:\db2.mdb;pwd=填入密码].表1
下面的算法可能并不省时,代码也多但能避免中间的中断
先对TABLEA和TABLEB排序
然后将两个表各放入一个链表
再将两个有序链表合并成一个有序链表
最后将有序链表存在数据库中
唯一的优点是事务分开执行每一步不太费时,
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b group by sfzh1 having count(*)>1)');
adoquery1.Open;
end;
begin
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('alter table b add idMytest int identity(1,1)');
adoquery1.ExecSQL; adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('delete from b where sfzh1 in (select sfzh1 from b where b.idMytest<idMytest '+
'group by sfzh1 having count(*)>1)');
adoquery1.ExecSQL; adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add('alter table b drop column idMytest');
adoquery1.ExecSQL;end;