improve transfer from dataset to a table lazarus / 16-Databases ---------------------------------------------------------------------- Bobito · 2019-08-29 16:22 This is part of my program where I need to save in a table called ventas_n two columns: cve_art (product_code) and cve_alterna (alternative_code) Later on I have to use this table in another query. //MSSQLConnection1 is a TMSSQLConnection; connection to ms sql server //SQLQuery1 is a TSQLQuery //SQLQuery2 is a TSQLQuery //SQLQuery1 has selected lots of records (about 5,000) from three (3) diffrent tables (facturas, remisiones y devoluciones -invoices, delivery notes and returns)  ...  // Delete any previous records in ventas_n,,.  SQLQuery2.Close;  SQLQuery2.DataBase := MSSQLConnection1;  SQLQuery2.SQL.Text := 'delete from ventas_n;';  SQLQuery2.ExecSQL;  SQLQuery1.First;  SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)';  While Not(SQLQuery1.EOF) do   begin    c_art   := SQLQuery1.FieldByName('cve_art').AsString;    c_alter := SQLQuery1.FieldByName('alterna').AsString;    SQLQuery2.Params.ParamByName('value1').AsString := c_art;    SQLQuery2.Params.ParamByName('value2').AsString := c_alter;    try     SQLQuery2.Close;     SQLQuery2.ExecSQL;    finally     // No additional action to be taken    end;    cant_reg := cant_reg + 1;    SQLQuery1.Next;   end;  ... You can imagine that going through the whole SQLQuery1 by: While Not(SQLQuery1.EOF) do begin ... end takes some time, and I would like to try another approach. I have read that a) select ... into table_b from table_a is desirable comapred to b) insert into table_b (...) SELECT ... from table_a ... I know in my case I do not have two tables, as a source I have a dataset. But I was wondering if there is another possible way to do it. Any suggestions? Thanks a lot. ---------------------------------------------------------------------- devEric69 · 2019-08-29 17:34 You tested if you could code everything at once,  with a single SQL statement such as... Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i "INSERT INTO table_destination (`destination fields`) SELECT `source fields`  FROM table_sourceWHERE `filter to get the right values`" ...? ---------------------------------------------------------------------- Bobito · 2019-08-29 18:16 Thanks devEric69, I have considered your hint. The thing is I don´t have two tables. The source comes from a dataset and the destinations certainly is a table. Of course I wish I could do it by means of a single SQL statement, but I don't know how to do that if my source is not a table but a dataset. I´ll keep investigating ... ---------------------------------------------------------------------- mangakissa · 2019-08-30 06:41 I don't see your transaction on SQLQuery2. But ik looks like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.First; SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)'; While Not SQLQuery1.EOF do  begin   SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;   SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;   SQLQuery2.ExecSQL;   .applyupdates;   SQLQuery2.commitretaining;   cant_reg := cant_reg + 1;   SQLQuery1.Next;  end; ---------------------------------------------------------------------- guest48180 · 2019-08-30 19:53 Quote from: mangakissa on August 30, 2019, 08:41:47 amI don't see your transaction on SQLQuery2. But ik looks like this: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.First; SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)'; While Not SQLQuery1.EOF do  begin   SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;   SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;   SQLQuery2.ExecSQL;   .applyupdates;   SQLQuery2.commitretaining;   cant_reg := cant_reg + 1;   SQLQuery1.Next;  end;  Actually, that would be Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.First; SQLQuery2.SQL.Text := 'insert into ventas_n (cve_art, cve_alterna) values (:value1,:value2)'; While Not SQLQuery1.EOF do  begin   SQLQuery2.Params.ParamByName('value1').AsString := SQLQuery1.FieldByName('cve_art').AsString;   SQLQuery2.Params.ParamByName('value2').AsString := SQLQuery1.FieldByName('alterna').AsString;   SQLQuery2.ExecSQL;   SQLQuery2.applyupdates;        // You had these    Transaction1.commitretaining;   // two reversed, mangakissa     cant_reg := cant_reg + 1;   SQLQuery1.Next;  end; ---------------------------------------------------------------------- LemonParty · 2019-08-30 20:17 Make a single query, like this: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO tb(fld1,fld2) VALUES(1,2),(3,5),...(14,100) ---------------------------------------------------------------------- kpeters58 · 2019-09-13 19:44 In no particular order of importance: 1) dis- & re-enable controls on query/dataset/table component before cycling through (if required) 2) don't use FieldByName().AsXXXX - that requires a lookup into the field defs and a typecast as well; use .Value if possible;     this has repeatedly made dramatic speed differences for us in Delphi - assume it would do so here as well 3) do only one commit (and not one after every record)! Or, if it makes you feel better, do one for every 100 or 1.000 records