//{一段Delphi+sql server 的例子,在不同数据库间Copy数据,有较强容错性。}procedure TH_FormCopyDb.CopyDB(query1, query2: Tquery; tablename,jgid: string);
var
i:integer;
SL:Tstringlist;
tempsql:string;
begin
SL:=Tstringlist.Create;
with query1 do
begin
if active then close;
sql.Text:='select * from '+tablename;
open;
end;
with query2 do
begin
if active then close;
sql.Text:='select * from '+tablename;
open;
end;
for i:=0 to query1.Fields.Count-1 do
if not(query2.FindField(query1.Fields[i].FullName)=nil) then
SL.Add(query1.Fields[i].fieldname);
if SL.Count<1 then
begin
sl.Free;
errorsl.Add(tablename+'没有字段一致!');
exit;
end;
query1.First;
while not query1.Eof do
begin
with query2 do
begin
close;
tempsql:='insert into '+tablename+' (';
for i:=0 to SL.Count-2 do
tempsql:=tempsql+trim(sl.Strings[i])+',';
tempsql:=tempsql+trim(sl.Strings[sl.count-1])+') values(';
for i:=0 to SL.Count-2 do
begin
if (Uppercase(sl.Strings[i])='JIGOUID') and (jgid<>'') then
begin
tempsql:=tempsql+'"'+jgid+'",';
end
else
begin
if query1.FieldByName(trim(sl.Strings[i])).DataType=ftBoolean then
begin
if query1.fieldbyname(trim(sl.Strings[i])).asboolean then
tempsql:=tempsql+'"1",'
else
tempsql:=tempsql+'"0",';
end
else
begin
if query1.FieldByName(trim(sl.Strings[i])).DataType in [ftCurrency] then
tempsql:=tempsql+query1.fieldbyname(trim(sl.Strings[i])).asstring+','
else
tempsql:=tempsql+'"'+query1.fieldbyname(trim(sl.Strings[i])).asstring+'",';
end;//end of if then
end;//end of if then
end; //end of while
if (Uppercase(sl.Strings[sl.count-1])='JIGOUID') and (jgid<>'') then
begin
tempsql:=tempsql+'"'+jgid+'")';
end
else
begin
if query1.FieldByName(trim(sl.Strings[sl.count-1])).DataType in [ftBoolean] then
begin
if query1.fieldbyname(trim(sl.Strings[sl.count-1])).asboolean then
tempsql:=tempsql+'"1")'
else
tempsql:=tempsql+'"0")';
end
else
begin
if query1.fieldbyname(trim(sl.Strings[sl.count-1])).DataType in [ftCurrency] then
tempsql:=tempsql+query1.fieldbyname(trim(sl.Strings[sl.count-1])).asstring+')'
else
tempsql:=tempsql+'"'+query1.fieldbyname(trim(sl.Strings[sl.count-1])).asstring+'")';
end;
end;
sql.Text:=tempsql;
try
execsql;
except
//出错处理
errorsl.Add(tempsql);
errorsl.Add('********************************************');
end;
end;
query1.next;
end;
SL.Free;
end;
var
i:integer;
SL:Tstringlist;
tempsql:string;
begin
SL:=Tstringlist.Create;
with query1 do
begin
if active then close;
sql.Text:='select * from '+tablename;
open;
end;
with query2 do
begin
if active then close;
sql.Text:='select * from '+tablename;
open;
end;
for i:=0 to query1.Fields.Count-1 do
if not(query2.FindField(query1.Fields[i].FullName)=nil) then
SL.Add(query1.Fields[i].fieldname);
if SL.Count<1 then
begin
sl.Free;
errorsl.Add(tablename+'没有字段一致!');
exit;
end;
query1.First;
while not query1.Eof do
begin
with query2 do
begin
close;
tempsql:='insert into '+tablename+' (';
for i:=0 to SL.Count-2 do
tempsql:=tempsql+trim(sl.Strings[i])+',';
tempsql:=tempsql+trim(sl.Strings[sl.count-1])+') values(';
for i:=0 to SL.Count-2 do
begin
if (Uppercase(sl.Strings[i])='JIGOUID') and (jgid<>'') then
begin
tempsql:=tempsql+'"'+jgid+'",';
end
else
begin
if query1.FieldByName(trim(sl.Strings[i])).DataType=ftBoolean then
begin
if query1.fieldbyname(trim(sl.Strings[i])).asboolean then
tempsql:=tempsql+'"1",'
else
tempsql:=tempsql+'"0",';
end
else
begin
if query1.FieldByName(trim(sl.Strings[i])).DataType in [ftCurrency] then
tempsql:=tempsql+query1.fieldbyname(trim(sl.Strings[i])).asstring+','
else
tempsql:=tempsql+'"'+query1.fieldbyname(trim(sl.Strings[i])).asstring+'",';
end;//end of if then
end;//end of if then
end; //end of while
if (Uppercase(sl.Strings[sl.count-1])='JIGOUID') and (jgid<>'') then
begin
tempsql:=tempsql+'"'+jgid+'")';
end
else
begin
if query1.FieldByName(trim(sl.Strings[sl.count-1])).DataType in [ftBoolean] then
begin
if query1.fieldbyname(trim(sl.Strings[sl.count-1])).asboolean then
tempsql:=tempsql+'"1")'
else
tempsql:=tempsql+'"0")';
end
else
begin
if query1.fieldbyname(trim(sl.Strings[sl.count-1])).DataType in [ftCurrency] then
tempsql:=tempsql+query1.fieldbyname(trim(sl.Strings[sl.count-1])).asstring+')'
else
tempsql:=tempsql+'"'+query1.fieldbyname(trim(sl.Strings[sl.count-1])).asstring+'")';
end;
end;
sql.Text:=tempsql;
try
execsql;
except
//出错处理
errorsl.Add(tempsql);
errorsl.Add('********************************************');
end;
end;
query1.next;
end;
SL.Free;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货