Board index » delphi » Local SQL INSERT

Local SQL INSERT

Hi!
I was wondering if it is possible to insert multiple rows in local SQL?
for ex:
INSERT
INTO "F:\test.db" (ADate, Description)
VALUES ('12/03/1998', 'awefqwerqwer')

 inserts only one row.
I want something like this:

INSERT
INTO "F:\test.db" (ADate, Description)
VALUES ('12/03/1998', 'awefqwerqwer')
VALUES ('12/03/1998', 'awefqwerqwer'
VALUES ('12/03/1998', 'awefqwerqwer')
VALUES ('12/03/1998', 'awefqwerqwer')

Thanks for help.

 

Re:Local SQL INSERT


Sql doesn't support it but you could write a little routine in Delphi to do
it for you:

var
    i : integer;
begin
     for i := 1 to 3 do
    begin
            Query1.Sql.Clear;
            Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
            Query1.Sql.Add(' Values(:ADATE,:DESC)');
            Query1.ParamByName('ADATE').AsDate := StrToDate('12/03/1998');
            Query1.ExecSql;
    end;
end;
--
Michael Glatz
mgl...@caiso.com

Quote
Nick wrote in message <77cshs$n...@forums.borland.com>...
>Hi!
>I was wondering if it is possible to insert multiple rows in local SQL?
>for ex:
>INSERT
>INTO "F:\test.db" (ADate, Description)
>VALUES ('12/03/1998', 'awefqwerqwer')

> inserts only one row.
>I want something like this:

>INSERT
>INTO "F:\test.db" (ADate, Description)
>VALUES ('12/03/1998', 'awefqwerqwer')
>VALUES ('12/03/1998', 'awefqwerqwer'
>VALUES ('12/03/1998', 'awefqwerqwer')
>VALUES ('12/03/1998', 'awefqwerqwer')

>Thanks for help.

Re:Local SQL INSERT


Hi Michael!
Thanks for reply! Would this be any faster then just using TTable?
The reason I wanted to do it via SQL is the speed. I need to insert
thousands of records and diong it via TTable takes a bit of a time.
Thanks again.
Quote
>var
>    i : integer;
>begin
>     for i := 1 to 3 do
>    begin
>            Query1.Sql.Clear;
>            Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
>            Query1.Sql.Add(' Values(:ADATE,:DESC)');
>            Query1.ParamByName('ADATE').AsDate := StrToDate('12/03/1998');
>            Query1.ExecSql;
>    end;
>end;

Re:Local SQL INSERT


Try using client data sets... or cached updates... I think this will speed
things up a bit.

  cuski.vcf
< 1K Download

Re:Local SQL INSERT


Hi Carmen!
Thanks I'll give it a try!

Re:Local SQL INSERT


Quote
In article <77dfbc$o...@forums.borland.com>, n...@yahoo.net (Nick) wrote:
> Hi Michael!
> Thanks for reply! Would this be any faster then just using TTable?
> The reason I wanted to do it via SQL is the speed. I need to insert
> thousands of records and diong it via TTable takes a bit of a time.

In that case, you'll find this approach faster:

var
  i : integer;
begin
  Query1.SQL.Clear;
  Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
  Query1.Sql.Add(' Values(:ADATE,:DESC)');

  Query1.Prepare;

  for i:=1 to 1000 do
  begin
    Query1.ParamByName('ADATE').AsDate:=StrToDate('12/03/1998');
    Query1.ParamByName('DESC').AsString:='whatever';
    Query1.ExecSQL;
  end;

  Query1.Unprepare;

end;

note that, if any of those parameters remain constant, then only set them
up once outside of your loop, but after the Prepare statement. Thus:

var
  i : integer;
begin
  Query1.SQL.Clear;
  Query1.Sql.Add('insert into TEST(ADATE,DESCRIPTION)');
  Query1.Sql.Add(' Values(:ADATE,:DESC)');

  Query1.Prepare;
  Query1.ParamByName('DESC').AsString:=
    'some text that never changes';

  for i:=1 to 1000 do
  begin
    Query1.ParamByName('ADATE').AsDate:=StrToDate('12/03/1998');
    Query1.ExecSQL;
  end;

  Query1.Unprepare;

end;

and finally, if you really need to wring all the speed out of it that you
can, replace the ParamByName('ADATE').AsDate type bits with

Params[0].AsDate

as Params is a zero-based index of the parameters parsed from the query. Of
course, this makes your code harder to read/maintain and might cause bugs
to creep in should you change your SQL later on in life, but it does mean
that Delphi isn't scanning the Params list for an entry 'ADATE'.

You must remember to call Unprepare after you've done though (and before
you do a Query1.SQL.Clear, more SQL etc).

Hope that helps

Rob
--
r...@robsoft.co.uk
http://www.robsoft.co.uk

Other Threads