Recent

Author Topic: Read One Table. Manipulate. Store In New Table  (Read 2845 times)

TomTech71

  • Newbie
  • Posts: 4
Read One Table. Manipulate. Store In New Table
« on: November 24, 2014, 08:28:41 pm »
Using the following components with Lazarus/FPC/Win7:

TSQLQuery/TDataSource/TSQLTransaction/TMSSQLConnection/TDBGrid

I already have working form/code that reads a MSSQL Server 2008 large table to the grid, filtered down using a Select Statement.

And I have separate code that successfully creates and populates new tables to a separate DB in the same MSSQL instance. Not using the grid data; just sample code that shows I can successfully create/populate a table on demand.

Putting it all together, what I'm actually after is the following: Read the requested 'Select From Where' information from the main table to a 'temp' recordset inside the program. This recordset will tie to the grid. One manipulates the data in the grid as needed. When done, this data IS NOT written back to the main table. The desire is for a command button to get clicked that will then take the resulting 'temp' recordset and create a new table in the database as mentioned above.

I really don't need full code...just a block diagram, or such, that shows what components I should be using, and their association to each other, and the basic flow of things. I expect I can work out the details once I have that.

Thanks

Tom

mangakissa

  • Hero Member
  • *****
  • Posts: 1113
Re: Read One Table. Manipulate. Store In New Table
« Reply #1 on: November 25, 2014, 09:04:20 am »
As long as you don't use applyupdates all changed records are in memory.
After you finished it is easy to copy your records to a different table.
But I think it's easier to make another query in TSQLQuery.InsertSQL and TSQLUpdateSQL.
These queries will be used when you apply the modifications to your database.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

TomTech71

  • Newbie
  • Posts: 4
Re: Read One Table. Manipulate. Store In New Table
« Reply #2 on: November 25, 2014, 08:52:14 pm »
Thanks for the reply.

Kinda been experimenting with that concept, but cant figure out exactly whose holding the temp data at that point.

When I write my Insert query, who am I pointing to for my source?

Thanks

Tom

mangakissa

  • Hero Member
  • *****
  • Posts: 1113
Re: Read One Table. Manipulate. Store In New Table
« Reply #3 on: November 26, 2014, 09:00:13 am »
TSQLQuery is derived of TBufdataset. TBufdataset is a memorytable that's hold your data.
If TSQLQuery.SQL.Text = 'SELECT id,name,age OF table1' is your mainquery, You can create
Code: [Select]
 
TSQLQuery.UpdateSQL.Text := 'INSERT INTO table2 (id,name,age) VALUES (:id,:name,:age)'
TSQLQuery.InsertSQL.Text := 'INSERT INTO table2 (id,name,age) VALUES (:id,:name,:age)'.
After using TSQLQuery.ApplyUpdates TSQLQuery will use InsertSQL and UpdateSQL to write queries to save the records to another table. 
But the best thing is to create a new TSQLQuery components which writes all your records sequently to your new table.
Code: [Select]
procedure SaveToOtherTable;
var MyInsertQuery : TSQLQuery;
    MyTransAction : TSQLTransaction;
begin
  MyInsertQuery := TSQLQuery.create(nil);
  MyTransAction := TSQLTransaction.create(nil);
  try
    MyInsertQuery.connection := <your database connection>;
    MyTransAction.connection := <your database connection>;
    MyInsertQuery.Transaction := MyTransAction;
    MyInsertQuery.SQL.Text := 'INSERT INTO table2 (name,age) VALUES (:name,:age)';
    MySelectQuery.first;
    while not MySelectQuery.eof do
    begin
      MyInsertQuery.Params[0].AsString := MySelectQuery.fields[0].AsString;
      MyInsertQuery.Params[1].AsInteger := MySelectQuery.fields[1].AsInteger;
      MyInsertQuery.ExecSQL;
      MySelectQuery.Next;
    end;
    MyInsertQuery.ApplyUpdates;
    MyTransAction.Commit;
  finally
    MyInsertQuery.free;
    MyTransAction.free;
  end;
end;
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

TomTech71

  • Newbie
  • Posts: 4
Re: Read One Table. Manipulate. Store In New Table
« Reply #4 on: November 28, 2014, 10:16:33 pm »
Thanks Once Again For The Info...

I seem to be having success now...

Tom

 

TinyPortal © 2005-2018