Batch insert/update

[tatamata]

Hello.
Could someone point me to direction how to insert many records in one operation (batch insert) to PostgreSQL, using Lazarus and Zeos?
I would appreciate an example.
Thanks.

[gto]

Put a TZSqlProcessor, link it to a TZConnection, fill up the SQL property of processor, separating the operations with the delimiter (specified in processor's properties) and call Processor.ExecSQL.

Saving any name typo (I'm without any IDE right now), that's all!

[btrewern]

If you are using a (very) new version of PostgreSQL (not sure if it's 8.1 or 8.2 but) you can use the multiple rows version of INSERT. example from the Postgresql docs:

Code: Select all

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

This should be faster than seperate inserts.

If you do use lots of inserts, wrap say 1000 of them in a transaction. This should speed thing up considerably as PostgreSQL has a high transaction overhead.

Regards,

Ben

[tatamata]

Hi!
In fact, I was trying to batch insert data from a .csv file to a PostgreSQL table, by looping through both datasets and appending rows from .csv file to PostgreSQL table.
The following code works:

procedure TForm1.ButtonImportOrdersClick(Sender: TObject);
begin
//Start transaction?
//ZConnection1.AutoCommit:=False;
//ZConnection1.StartTransaction;

//Delete all from table "orders".
//ZTableOrdersPostgreSQL.Edit;
ZTableOrdersPostgreSQL.EmptyDataSet;
//ZTableOrdersPostgreSQL.Post;

ZTableOrdersPostgreSQL.Refresh;

//Enable cached updates.
ZTableOrdersPostgreSQL.CachedUpdates:=True;

//Go to the first row in both tables.
ZTableOrdersPostgreSQL.First;
SdfDataSetOrdersCsv.First;

//Disable controls.
//ZTableOrdersPostgreSQL.DisableControls;
while not SdfDataSetOrdersCsv.EOF do
begin
//Enable appending new data.
ZTableOrdersPostgreSQL.Append;

//Set field values.
//If SdfDataSetOrdersCsv.FieldByName('Sys.status') like '%CNF%' THEN
if pos(' CNF ', SdfDataSetOrdersCsv.FieldByName('Sys.status').AsString)<>0 then
ZTableOrdersPostgreSQL.FieldByName('order_confirmed_finally').AsBoolean:=True
else
ZTableOrdersPostgreSQL.FieldByName('order_confirmed_finally').AsBoolean:=False;
ZTableOrdersPostgreSQL.FieldByName('order').AsString:= SdfDataSetOrdersCsv.FieldByName('Order').Value;
ZTableOrdersPostgreSQL.FieldByName('product').AsString:= SdfDataSetOrdersCsv.FieldByName('Material').Value;
ZTableOrdersPostgreSQL.FieldByName('product_description').AsString:= SdfDataSetOrdersCsv.FieldByName('Mat.descr.').Value;
ZTableOrdersPostgreSQL.FieldByName('order_type').AsString:= SdfDataSetOrdersCsv.FieldByName('Order type').Value;
ZTableOrdersPostgreSQL.FieldByName('product_target_qty').AsVariant:= SdfDataSetOrdersCsv.FieldByName('Target qty').Value;
ZTableOrdersPostgreSQL.FieldByName('product_target_qty_units').AsString:= SdfDataSetOrdersCsv.FieldByName('Target qty1').Value;
ZTableOrdersPostgreSQL.FieldByName('order_planned_start').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Bas. start').Value;
ZTableOrdersPostgreSQL.FieldByName('order_planned_finish').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Basic fin.').Value;
ZTableOrdersPostgreSQL.FieldByName('product_confirmed_qty').AsVariant:= SdfDataSetOrdersCsv.FieldByName('Conf. qty').AsVariant;
ZTableOrdersPostgreSQL.FieldByName('product_delivered_qty').AsVariant:= SdfDataSetOrdersCsv.FieldByName('Del. qty').AsVariant;
if SdfDataSetOrdersCsv.FieldByName('Created on').AsString <>'' then
ZTableOrdersPostgreSQL.FieldByName('order_created_date').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Created on').Value;
if SdfDataSetOrdersCsv.FieldByName('Release').AsString<>'' then
ZTableOrdersPostgreSQL.FieldByName('order_released_date').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Release').Value;
if SdfDataSetOrdersCsv.FieldByName('Act. start').AsString <>'' then
ZTableOrdersPostgreSQL.FieldByName('order_actual_start').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Act. start').Value;
if SdfDataSetOrdersCsv.FieldByName('Act.finish').AsString<>'' then
ZTableOrdersPostgreSQL.FieldByName('order_actual_finish').AsDateTime:= SdfDataSetOrdersCsv.FieldByName('Act.finish').Value;
ZTableOrdersPostgreSQL.FieldByName('order_status').AsString:= SdfDataSetOrdersCsv.FieldByName('Sys.status').AsString;
ZTableOrdersPostgreSQL.FieldByName('plant').AsString:= 'NE05';
if SdfDataSetOrdersCsv.FieldByName('Order type').AsString='PI01' THEN
ZTableOrdersPostgreSQL.FieldByName('order_active').AsBoolean:=True
else
ZTableOrdersPostgreSQL.FieldByName('order_active').AsBoolean:=False;

//Post appended row.
ZTableOrdersPostgreSQL.Post;

//Move to next row in both tables.
SdfDataSetOrdersCsv.Next;
ZTableOrdersPostgreSQL.Next;
end;

//Apply appended row (cached update).
ZTableOrdersPostgreSQL.ApplyUpdates;
ZTableOrdersPostgreSQL.CommitUpdates;

//Commit transaction?
//ZConnection1.Commit;

//Enable controls.
ZTableOrdersPostgreSQL.EnableControls;
end;

But,

1. I'm not shure whether CachedUpdates insert value row by row, or the whole statement (all rows at once). Why do I need CommitUpdates after ApplyUpdates?
2. If I try to uncomment "...StartTransaction....Commit" I get error that operation is not allowed for NonAutoCommit...". What operation?
3. Should I use string concatenation that would create SQL string for INSERT INTO, from the .csv file, as btrewern suggests, instead?

Can someone explain me what is CachedUpdate and whether that is what I need, or I need StartTransaction/Commit methods of connection object.
I need something that will behave like TBatchMove in Delphi...

Regards,

Zlatko

[mdaems]

1) Row by row.
2) Starttransaction is not allowed when you're not in autocommit mode. In autocommit mode it can be used to group some statements that should be committed at once.
3) CachedUpdates means there is no post of changes until you ask for it by Applyupdates.

[tatamata]

Hi!
1. What is the difference between ApplyUpdates and CommitUpdates?
2. If cahced updates inserts row by row, is there any other way to insert rows in a batch?

Regards,

Zlatko