Board index » delphi » Inserting Data into Two Different Tables by querying from a single Table

Inserting Data into Two Different Tables by querying from a single Table

Hi,
    I am trying to Insert Data into Two Different Tables by querying from a
single Table,I find that query live is not going to help me,Cached Update is
also not suitable for me,because it is going to update data in single
transaction.
Is their any other way of doing it .

the options that flashing to me are
1.using a stringlist and constructing insert statements
2.have two result sets each for a bate then try update.

please tell me which is the best of doing this kind of insertion.

TIA.
kishor.
kis...@icodeindia.soft.net

 

Re:Inserting Data into Two Different Tables by querying from a single Table


Quote
>Cached Update is
>also not suitable for me,because it is going to update data in single
>transaction.
>Is their any other way of doing it .

No.  Just write the SQL how you want it and run it from the OnUpdateRecord
event.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Inserting Data into Two Different Tables by querying from a single Table


In article <7di07b$1...@forums.borland.com>,
  "kishor" <kishor...@mailcity.com> wrote:

Quote
> Hi,
>     I am trying to Insert Data into Two Different Tables by querying from a
> single Table,I find that query live is not going to help me,

Why not?  All you would need to do with your query live is to create another
query object that would insert the records into your 2nd table in the first
query's BeforePost event:

Example:

procedure TForm1.Query1BeforePost(DataSet: TDataSet);
var
   qry: tQuery;
begin
   qry := TQuery.Create(Self);
   qry.DatabaseName := Query1.DatabaseName;
   qry.Sql.Clear;
   qry.SQL.add('Insert into Employee1(LastName,FirstName) Values('+
       ':Ln,:FN)');
   qry.ParamByName('LN').AsString := Query1.FieldByName('LastName').AsString;
   qry.ParamByName('FN').AsString := Query1.fieldByName('FirstName').AsString;
   qry.Prepare;
   qry.ExecSQL;
end;

Quote
>Cached Update is
> also not suitable for me,because it is going to update data in single
> transaction.

If you use CachedUpdates you can do the very same thing as above.  Plus there
is also available a variant value for all fields called OldValue that allows
you to see what the value was before a user changed it (if you are updating
records).

Michael Glatz
glatzfa...@aol.com
mgl...@caiso.com

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Re:Inserting Data into Two Different Tables by querying from a single Table


On Sat, 27 Mar 1999 12:40:19 +0530, "kishor" <kishor...@mailcity.com>
wrote:

Quote
>I am trying to Insert Data into Two Different Tables by querying from a
>single Table,I find that query live is not going to help me,Cached Update is
>also not suitable for me,because it is going to update data in single
>transaction.
>Is their any other way of doing it .

If I understand your dilemma correctly, you have a SELECT statement
retrieving data from two base tables, which produces a read-only result
set. You wish to use cached updates (or some equivalent) to update both
base tables using changes made in the one result set. Is this correct? If
so...

You can use cached updates for this. You just have to change your approach
a little from the way you would do it if only one base table was to be
updated. This process is described in the online help. In the table of
contents, navigate through the topic titles:

  Developing Database Applications
    Working with cached updates
      Using multiple update objects

The keys to this are:

1. Using one TUpdateSQL for each base table to be updated.
2. Creating a handler for the cached TQuery's OnUpdateRecord event.
3. Calling the Apply method of each TUpdateSQL used.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                    "My problem lies in reconciling my gross
Technical Publications            habits with my net income."
INPRISE Corporation                             -- Errol Flynn (1909-1959)
http://www.borland.com/delphi

Other Threads