Mass database SQL INSERT - best way 2

[AP81]

I have written an App which inserts about 5000 lines into the database. The way I do it is somthing like this:

//loop
    SQLInsertText := SQLInsertText +
                     '(' + QuotedStr(Invoice.CustomerCode) + ', ' + Invoice.TemplateNo +
                     ', ' +  QuotedStr('-') + ', ' + QuotedStr('-') + ', ' + QuotedStr('-') +
                     ', ' +  FloatToStr(0) + ', ' + Invoice.Amount + ', ' + QuotedStr(strTaxCode) +
                     ', ' + Invoice.Qty + ', ' + QuotedStr(Invoice.ItemCode) +
                     ', ' + QuotedStr(Invoice.InvDesc) + ', ' + QuotedStr('-') +
                     ', ' + QuotedStr(Invoice.StartDate) + ', ' + QuotedStr(Invoice.EndDate) +
                     ', ' + QuotedStr(Invoice.AutoRollover) +
                     ', ' + IntToStr(i) + '), ';


.....

    SQLInsertText := SQLInsertText +
                    '(' + QuotedStr(Invoice.CustomerCode) + ', ' + Invoice.TemplateNo +
                    ', ' + QuotedStr(Invoice.TemplateType) + ', ' + QuotedStr(Invoice.ToBePrinted) +
                    ', ' + QuotedStr(Invoice.InvMemo) + ', ' + FloatToStr(0) +
                    ', ' + Invoice.Amount + ', ' + QuotedStr(strTaxCode) + ', ' + QuotedStr('0') +
                    ', ' + QuotedStr('*header_line*') + ', ' + QuotedStr('*header_line*') +
                    ', ' + QuotedStr(Invoice.CustMsg) + ', ' + QuotedStr(Invoice.StartDate) +
                    ', ' + QuotedStr(Invoice.EndDate) + ', ' + QuotedStr(Invoice.AutoRollover) +
                    ', ' + IntToStr(i) + ')';

....

    try
      //Add header data to database
      AddInvoicesToDB(SQLInsertText);
    except on e:exception do
      begin
        MessageDlg('Error' + #13#10 + #13#10 + e.Message, mtInformation, [mbOK], 0);
        exit;
      end
    end;


//procedure to add extracted invoice information to database
procedure TfrmMain.AddInvoicesToDB(SQL : String);
begin
  ADOCommandInsert.CommandText := 'INSERT INTO invoice_data VALUES ' +
                                  SQL;
  ADOCommandInsert.Execute;
end;

This is all working okay, however I would like an opinion on whether there is a better or more efficient way.

Thanks.




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[TheBugSlayer]

The loop part of your question is not very clear. Do you loop 5000 times inserting a record each time? Where does the data use in the INSERT come from?

There is certainly a more efficient way, if it runs on the server as opposed to from with Delphi it should be more efficient but I can't tell you much until I understand your code. Provie a pseudocode or the comple code.

 

[Griffyn]

The SQL 'INSERT' command can insert either a single record via the 'VALUES' operator as you have used, or it can insert an entire dataset (from a table or query) which I'm sure would be much more efficient. If the data you're inserting is coming from a database table, then you should try and do this.

 

[richardchaven]

Aside from using a DBMS-specific command (e.g. LOAD) and INSERTing data from other tables, you have to insert things one row at a time.

However, you can use query parameters instead of composing the text lexagraphically:

with ThisQuery do
begin
    SQL.Text := 'INSERT INTO ' + TableName  + ' VALUES(Column1, Column6, CreatedDate) (:Value1, :Value6, :Date)';
    Prepare;

    for Counter := 0 to Somthing.Count - 1 do
    begin
        ParamByName('Value1').AsString := Something[Counter].First;
        ParamByName('Value6').AsString := Something[Counter].Sixth;
        ParamByName('Date').AsDateTime := Now;

        ExecSQL;
    end;
end;

Cheers

 

[AP81]

richardchaven & Griffyn, thanks for clearing that up. That is what I am aiming for.

My application basically reads an xml file (about 5 meg) from Quickbooks. It then opens the xml file, strips out all the information, the adds is to the database (each time it reaches a certain xml tag) continually until it reaches the end of the file.

I am aiming to make it look more logical and a make it a little more efficient, as the loop will continually add all the information into the database until the end of the file is reached (which is about 3000 times).

Is it possible to use query parameters to load the information into a dataset?

Thanks for your help guys, it has pointed me in the right direction.




------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[AP81]

OK, here is a different segment of code my application which saves information from a stringgrid into a MySQL database.

The problem is that I read the data from each row and post it to the database after every row. So if there are 15 rows in the stringgrid, then I will make 15 posts to the database.

My question is is there a way I can post all the information in one go?

Here is my code:

//save an invoice, given any StringGrid and customerCode
Procedure TfrmMain.SaveInvoice(sg : TStringGrid; CustCode, Flag : String;
                         StartMonth, StartYear, EndMonth, EndYear : TComboBox;
                         EndDate, Rollover : TRadioButton;
                         memo, total : TEdit; CustMsg : TMemo;
                         ToBePrinted : TCheckBox);
var
  i : Integer;
  Invoice : TInvoice;
  SQLInsertText, strTaxCode : String;
begin
  //assign customer code,template no and generation date
  Invoice.CustomerCode := CustCode;
  Invoice.TemplateNo := Flag;
  Invoice.StartDate := StartMonth.Text + ' ' + StartYear.Text;
  if Rollover.Checked then
    begin
      Invoice.EndDate := ReturnEndDate(StartMonth, StartYear);
      Invoice.AutoRollover := 'Y';
    end
  else if optImportEndDateA.Checked then
    begin
      Invoice.EndDate := EndMonth.Text + ' ' + EndYear.Text;
      Invoice.AutoRollover := 'N';
    end;

  //set status bar
  pbMain.Max := TruncateInvoice(sg);
  lblMsg.Caption := 'Saving Invoice';
  //loop through StringGrid and retreive all information
  for i := 0 to pbMain.Max do    //last row containing text
    begin
      if (sg.Cells[0,i] = '') then
        begin
          Invoice.Qty := '0';
          Invoice.Amount := '0';
          if (sg.Cells[1,i] = '') then
            begin
              if (sg.Cells[1,i] = '') AND (sg.Cells[2,i] <> '') then
                begin
                  Invoice.Qty := '0';
                  Invoice.ItemCode := '*text*';
                  Invoice.InvDesc := sg.Cells[2,i];
                  Invoice.Amount := '0';
                  strTaxCode := 'N/A';
                end
              else
                begin
                  Invoice.Qty := '0';
                  Invoice.ItemCode := '--Blank Line--';
                  Invoice.InvDesc := '--Blank Line--';
                  Invoice.Amount := '0';
                  strTaxCode := 'N/A';
                end;
            end;
        end
      else
        begin
          Invoice.Qty := sg.Cells[0,i];
          Invoice.ItemCode := sg.Cells[1,i];
          Invoice.InvDesc := sg.Cells[2,i];
          strTaxCode := sg.Cells[3, i];
          Invoice.Amount := sg.Cells[4,i];
        end;

    ADOCommandInsert.CommandText := 'insert into invoice_data (cust_code, temp_no, ' +
                                    'temp_type, to_be_printed, inv_memo, tax_total, amount, ' +
                                    'taxcode, qty, item_code, inv_desc, cust_msg, start_date, ' +
                                    'end_date, rollover, rowpos) values (:custcode, ' +
                                    ':tempno, :temptype, :tobeprinted, :memo, :taxtotal, ' +
                                    ':amount, :taxcode, :quantity, :itemcode, :desc, :custmsg, ' +
                                    ':startdate, :enddate, :autorollover, :rowpos)';

    ADOCommandInsert.Parameters.ParamByName('custcode').Value := Invoice.CustomerCode;
    ADOCommandInsert.Parameters.ParamByName('tempno').Value := Invoice.TemplateNo;
    ADOCommandInsert.Parameters.ParamByName('temptype').Value := '-';
    ADOCommandInsert.Parameters.ParamByName('tobeprinted').Value := '-';
    ADOCommandInsert.Parameters.ParamByName('memo').Value := '-';
    ADOCommandInsert.Parameters.ParamByName('taxtotal').Value := '0.00';
    ADOCommandInsert.Parameters.ParamByName('amount').Value := Invoice.Amount;
    ADOCommandInsert.Parameters.ParamByName('taxcode').Value := strTaxCode;
    ADOCommandInsert.Parameters.ParamByName('quantity').Value := Invoice.Qty;
    ADOCommandInsert.Parameters.ParamByName('itemcode').Value := Invoice.ItemCode;
    ADOCommandInsert.Parameters.ParamByName('desc').Value := Invoice.InvDesc;
    ADOCommandInsert.Parameters.ParamByName('custmsg').Value := '-';
    ADOCommandInsert.Parameters.ParamByName('startdate').Value := Invoice.StartDate;
    ADOCommandInsert.Parameters.ParamByName('enddate').Value := Invoice.EndDate;
    ADOCommandInsert.Parameters.ParamByName('autorollover').Value := Invoice.AutoRollover;
    ADOCommandInsert.Parameters.ParamByName('rowpos').Value:= IntToStr(i);
    ADOCommandInsert.Execute;

    pbMain.StepIt;    //step progress
    strTaxCode := 'N/A';
    end;
    //-->proceed with adding header information to the database
    //get memo, total cost and IsToBePrinted
    //Invoice.InvMemo := Trim(Memo.Text);
    Invoice.InvMemo := Memo.Text;
    if (Total.Text = '') then
      begin
        Invoice.Amount := '0';
      end
    else
      begin
        Invoice.Amount := Total.Text;
      end;
    Invoice.CustMsg := Trim(custmsg.Lines.Text);
    if ToBePrinted.Checked then
      begin
        Invoice.ToBePrinted := 'true';
      end
    else
      begin
        Invoice.ToBePrinted := 'false';
      end;
    Invoice.TemplateType := cboTemplateType.Text;

    ADOCommandInsert.CommandText := 'insert into invoice_data (cust_code, temp_no, ' +
                                    'temp_type, to_be_printed, inv_memo, tax_total, amount, ' +
                                    'taxcode, qty, item_code, inv_desc, cust_msg, start_date, ' +
                                    'end_date, rollover, rowpos) values (:custcode, ' +
                                    ':tempno, :temptype, :tobeprinted, :memo, :taxtotal, ' +
                                    ':amount, :taxcode, :quantity, :itemcode, :desc, :custmsg, ' +
                                    ':startdate, :enddate, :autorollover, :rowpos)';

      ADOCommandInsert.Parameters.ParamByName('custcode').Value := Invoice.CustomerCode;
      ADOCommandInsert.Parameters.ParamByName('tempno').Value := Invoice.TemplateNo;
      ADOCommandInsert.Parameters.ParamByName('temptype').Value := Invoice.TemplateType;
      ADOCommandInsert.Parameters.ParamByName('tobeprinted').Value := Invoice.ToBePrinted;
      ADOCommandInsert.Parameters.ParamByName('memo').Value := Invoice.InvMemo;
      ADOCommandInsert.Parameters.ParamByName('taxtotal').Value := FloatToStr(0);
      ADOCommandInsert.Parameters.ParamByName('amount').Value := Invoice.Amount;
      ADOCommandInsert.Parameters.ParamByName('taxcode').Value := strTaxCode;
      ADOCommandInsert.Parameters.ParamByName('quantity').Value := '0.00';
      ADOCommandInsert.Parameters.ParamByName('itemcode').Value := '*header_line*' ;
      ADOCommandInsert.Parameters.ParamByName('desc').Value := '*header_line*';
      ADOCommandInsert.Parameters.ParamByName('custmsg').Value := Invoice.CustMsg;
      ADOCommandInsert.Parameters.ParamByName('startdate').Value := Invoice.StartDate;
      ADOCommandInsert.Parameters.ParamByName('enddate').Value := Invoice.EndDate;
      ADOCommandInsert.Parameters.ParamByName('autorollover').Value := Invoice.AutoRollover;
      ADOCommandInsert.Parameters.ParamByName('rowpos').Value := IntToStr(i);
      ADOCommandInsert.Execute;

    lblMsg.Caption := 'Save complete';
    pbMain.Position := 0;

    MessageDlg('Invoice for: ' + Invoice.CustomerCode +
               ' has been saved with TempNo : ' +
               Invoice.TemplateNo, mtInformation, [mbOK], 0);

    LoadCustomers; //reload information into comboboxes
    //display new template no
    lblShowTempNo.Caption := Invoice.TemplateNo;
    lblStatus.Caption := 'Status: Viewing';
    cboCustCode.Text := Invoice.CustomerCode;
    memoAddress.Text := RetreiveAddress(Invoice.CustomerCode);
end;

------------------------------------
There's no place like 127.0.0.1
------------------------------------

 

[towerbase]

I am not sure what you mean by " post all the information in one go". If "one go" means making one call to MySQL then this can be done by using the MySQL LOAD DATA statement.

However, you need to extract the data from your StringGrid first and store it in a text file which will be used by the LOAD DATA statement. This approach is likely to execute faster than using an INSERT statement for each line of the StringGrid but it is less portable as I doubt if any other DBMS implements an identical syntax LOAD DATA statement to MySQL.

The LOAD DATA statement has a number of options so you should refer to the MySQL documentation to choose the ones suitable for your requirements.

An appropriate form of the LOAD DATA statement to load a typical CSV format file might be

LOAD DATA LOCAL INFILE 'invoices.txt' INTO TABLE invoice_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'

The main part of your coding would be to extract the data from your StringGrid into a CSV file. This is not particularly difficult and there may be some components around to assist you.

At it's simplest your code might look something like:

procedure TForm1.CreateInvoiceCSVFile;
var
 row: integer;
 col: integer;
 csv: TStringGrid;
 data: string;
begin
 csv := TStringGrid.Create;
 try
  for row := 0 to sg.RowCount - 1 do 
  begin
   data := '';
   for col := 0 to sg.ColCount - 2 do
    data := data + QuotedStr(sg.cells[col,row]) + ',';
   csv.Add ( data + QuotedStr(sg.cells[sg.ColCount-1,row]);
  end;
  csv.SaveToFile('invoices.txt');
 finally
  csv.free;
 end;
end;

The above code assumes that you want every column of every row of your StringGrid to be imported into your MySQL table. This is probably an oversimplification.

I haven't tested this code so beware of typos ...


Andrew
Hampshire, UK