Delphi TAdoQuery - Multiple Inserts?

Question

Is it possible with a Delphi TAdoQuery to execute multiple inserts in one go, or do you have to execute each statement separately? What I want to do is this:

AdoQuery.SQL.Clear;
AdoQuery.SQL.Add('INSERT INTO user VALUES (1, "User 1");');
AdoQuery.SQL.Add('INSERT INTO user VALUES (2, "User 2");');
AdoQuery.SQL.Add('INSERT INTO user VALUES (3, "User 3");');
AdoQuery.ExecSQL;   
AdoQuery.Close;

Is this possible? I'm getting an error from MySQL when executing this. I also tried adding BEGIN; and END; around the queries, but that didn't work either.

Edit: I want to do this because when I execute the inserts in a for loop it seems like it takes a really long time for > 10 queries. I'm assuming adding them all like above would speed things up. Does anyone know if the AdoQuery.Close call is necessary between inserts?

Answer 1

With MySQL you can use the syntax:

INSERT INTO user VALUES (1, "User 1"), (2, "User 2"), (3, "User 3")

Then you may use parameters:

AdoQuery.SQL.Text := 'INSERT INTO user VALUES (:p11, :p12), (:p21, :p22), (:p31, :p32)';
AdoQuery.Parameters[0].Value := 1;
AdoQuery.Parameters[1].Value := 'User 1';
AdoQuery.Parameters[2].Value := 2;
AdoQuery.Parameters[3].Value := 'User 2';
AdoQuery.Parameters[4].Value := 3;
AdoQuery.Parameters[5].Value := 'User 3';
AdoQuery.ExecSQL;

Answer 2

Try something like this (with an AdoCommand):

sSql := 'INSERT INTO User (FieldName1, FieldName2) values (:Nr, :Strng)';
AdoCmd.Parameters.Clear();     
AdoCmd.CommandText := sSql;
AdoCmd.CommandType := cmdText;
AdoCmd.Parameters.ParseSQL( sSql, True );
AdoCmd.Parameters.ParamByName('Nr').DataType := ftInteger
AdoCmd.Parameters.ParamByName('Strng').DataType := ftString;

for i := 1 to 10 do
begin     
    AdoCmd.Parameters.ParamByName('Nr').Value := i;
    AdoCmd.Parameters.ParamByName('Strng').Value := sUserName(i);
    AdoCmd.Execute;
end;

You could speed up thing by using .Params(0) and .Params(1) because ParamByName takes up some time.

But the trick here is the ParseSql statement. It keeps your code clear but still only parses the sql string only once.

And you can use transactions if necessary ... by using AdoCmd.Connection.BeginTrans and AdoCmd.Connection.CommitTrans / RollbackTrans.

Answer 3

Proper use of transactions will also speed up your inserts. If each statement needs to be committed, it will take longer to be executed. If you can execute everything within a single transaction and just commit at the end it will be faster. Don't know MySQL, but some databases also support "array DML", where a single SQL statement is sent to the DB together array of parameters and thereby execute multiple times but with a single communication roundtrip.