Two tables

[Geoff Jones]

Hiya

I have a problem which I'm working on which I hope someone can help with. I
have two tables in a database. For the sake of argument, let us call them
Table1 and Table2.

For each record in Table1, I want to write a record in Table2 where the
record will be made up of information from the corresponding record in
Table1.

If the application is shutdown and additional records are added to Table1,
the next time the application is run, I want to add extra records to Table2
from these "new" records; preferbly by not completely re-writing Table2 i.e.
appending new records to its end.

Obviously, the application will need to able to "know" which are the new
records.

Can anybody help with this? Some example code would be most appreciated.

Thanks in advance

Geoff

 

[Cor Ligthert]

Geoff,

In my opinion is the most simple to use a boolean in table1

Than you read the table 1 where notmadetable2 = false.

You have to update table 1 than of course as well.

Probably is this not possible for you, however I start with the most simple
solution.

Cor

 

[Guest]

Geoff,

Is it a SQL database? If not you may be able to modify the statement below
to work for whatever database. I assume there is a key that links Table1 to
Table2, if so you could run some SQL as below.

Hope it helps.
Chris.

INSERT INTO Table2
(T2_Key
, T2_Field_1
, T2_Field_2
, T2_Field_3)
SELECT T1_Key
, T1_Field_1
, T1_Field_2
, T1_Field_3
FROM Table1
LEFT JOIN Table2 ON T2_Key = T1_Key
WHERE T2_Key IS NULL

 

[Geoff Jones]

Thanks Cris and Cor. Most useful suggestions.

Geoff

 

[Pete Wright]

Following on from Chris's answer, if you are working with a SQL Server
database, then perhaps it would be worthwhile writing a trigger to let the
database automatically replicate the data to the second table.