Read One Table. Manipulate. Store In New Table
lazarus / 16-Databases

----------------------------------------------------------------------
TomTech71 · 2014-11-24 19:28

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 · 2014-11-25 08:04

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.

----------------------------------------------------------------------
TomTech71 · 2014-11-25 19:52

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 · 2014-11-26 08:00

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;

----------------------------------------------------------------------
TomTech71 · 2014-11-28 21:16

Thanks Once Again For The Info...

I seem to be having success now...

Tom