Hello,
here are 3 simple procedures i use often to do the job:
// ------------------------------------------------------------------------------
procedure TMainForm.buildSql(tn: String; d: TDataSet; s: TStrings);
var
r: integer;
sl: TStringList;
const
LIMIT = 10000;
begin
sl:=TStringList.Create;
with d do begin
DisableControls;
r:=0;
first;
while not eof do
begin
// build sql-statement for insert into mysql-table
if r=0 then
begin
sl.Add(Format('replace into %s (%s)',[tn, getFieldNameList(d)]));
// sl.Add(Format('insert into %s (%s)',[tn, getFieldNameList(d)]));
sl.Add(Format(' values (%s)',[getValueList(d)]));
next;
inc(r);
end;
// build VALUES for every line in the resultset
while (not eof) and (r<LIMIT) do
begin
sl.Add(format(' ,(%s)',[getValueList(d)]));
next;
inc(r);
end;
sl.Add(';');
r:=0;
end;
EnableControls;
end;
s.assign(sl);
sl.Free;
end;
// ------------------------------------------------------------------------------
function TMainForm.getFieldNameList(d: TDataSet): String;
var
fl: TStringList;
f: TField;
begin
fl:=TStringList.Create;
for f in d.Fields do
fl.Add(f.FieldName);
result:=fl.CommaText;
fl.Free;
end;
// ------------------------------------------------------------------------------
function TMainForm.getValueList(d: TDataSet): String;
var
i: integer;
f: TField;
begin
result:='';
f:=d.Fields[0];
if f.DataType in [ftInteger] then result:=f.asString
else if f.DataType in [ftDate, ftDateTime]
then result:=QuotedStr(getSqlDateTime(f.asString))
else result:=QuotedStr(f.asString);
for i:=1 to d.Fields.Count-1 do
begin
f:=d.Fields;
if f.DataType in [ftInteger] then result:=result+','+f.asString
else if f.DataType in [ftDate, ftDateTime]
then result:=result+','+QuotedStr(getSqlDateTime(f.asString))
else result:=result+','+QuotedStr(f.asString);
end;
end;
// --------- helper function
function TMainForm.getSqlDateTime(s: String): String;
var
y,m,d,h,mi,se,ms: word;
dt: TDateTime;
begin
dt:=StrToDateTime(s);
DecodeDateTime(dt,y,m,d,h,mi,se,ms);
result:=Format('%.04d-%.02d-%.02d %.02d:%.02d:%.02d',[y,m,d,h,mi,se]);
end;
If you need to just insert the records, switch statement.
If you only want to save your query-result use another TQuery and set the SQL to:
qry.SQL.assign(yourResultQuery.SQL);
qry.SQL.Insert('CREATE TABLE [desired_tablename]', 0);
Good Luck :-)