I have 2 tables called Master and Transaction (using v3.27). The
Transaction table records are linked to the Master table by a field
called JobID.

I want to acrhive (copy) some Master records into another table called
MasterArchives (and then delete the original records from Master).
Then archive (copy) the linked Transaction records into another table
TransactionArchives (and delete the originals from table Transaction).

I have copied the Master table records to MasterArchives OK but I now
want to delete those records from table Master that are found in table
MasterArchives. The main index in both tables is field JobID. Can I do
this in SQL?

The next step would be to copy the Transaction records to the
TransactionArchives table that only have linked fields in table
MasterArchives (and then delete the copied records from table
Transaction).

I could do this using the longwinded approach of
with myTable do
 while not EOF do.....

but with 400,000 records in table Transaction its going to take me 7
hours plus

Any help in doing this with SQL would be MOST appreciated, or maybe
another way of doing this?

thank you,
Pat

Pat


Untested


DELETE FROM MASTER WHERE JobID IN (SELECT JobID FROM MasterArchives);
INSERT INTO TransactionArchives SELECT * FROM Transactions WHERE Transactions.JobID IN (SELECT JobID FROM MasterArchives);


Joins would probably be better, or it might be faster and more efficient to select the JobID into a temporary memory table and then use that to control the other actions.

Roy Lambert

hi Roy,

>DELETE FROM MASTER WHERE JobID IN (SELECT JobID FROM MasterArchives);
works like a bueaty

>INSERT INTO TransactionArchives SELECT * FROM Transactions WHERE Transactions.JobID IN (SELECT JobID FROM MasterArchives);
when I run this I get error #11949 SQL error - End of INSERT statement
expected, instead found 'IN'

I tried
WHERE JobCostsTran.JobID = 100
and this works OK

I tried modifying the WHERE and include the IN  but cannot get it
working. My actual code is

INSERT INTO ArchivedJobCostsTrans  
SELECT *
FROM JobCostsTran
WHERE JobCostsTran.JobID IN (SELECT JobID FROM ArchiveJobCosts)


>Joins would probably be better, or it might be faster and more efficient to select the JobID into a temporary memory table and then use that to control the other actions.
I will try once I fix my problem.

Thanks for your help Roy

Pat

Pat


I think its a limitation of V3 not being able to have a subselect in the insert statement. Depending on how many records are involved I think I'd use an approach like:

1. Select the job IDs to be transfered
2. Loop through that query building a comma separated string of the IDs (quoted if they're strings rather than integers)
3. Build the transfer/delete statements into a script using IN (the stringlist just built)
4. Run the sql

This can actually have performance benefits with getting a shorter list of IDs to be tested and you can wrap the script up in a single transaction.

Roy Lambert

hi Roy,

thanks again for the reply

> Depending on how many records are involved I think I'd use an approach like:
ArchivedJobcosts has 20,000 records
Transaction file has 400,000 records

I haven't done this way before so can I ask for a bit more guidance

>1. Select the job IDs to be transfered
got this by

begin
with  qry4 do
 begin
  tblArchivedJobCosts.Close;
  qry4.Close;
  SQL.Clear;
  SQL.Add('SELECT ArchivedJobCosts.JobID');
  SQL.Add('FROM ArchivedJobCosts');
  SQL.Add('ORDER BY ArchivedJobCosts.JobID');
  qry4.ExecSQL;
  ShowMessage('Records selected = ' + IntToStr(qry4.RecordCount));
 end;
end;

>2. Loop through that query building a comma separated string of the IDs (quoted if they're strings rather than integers)
is this comma separated string stored in a text file?
The ID is numeric.

with qry4 do
begin
 First;
 while not Eof do
  begin
   // build the string here I guess, but I do not know how
   Next;
  end;
end;

>3. Build the transfer/delete statements into a script using IN (the stringlist just built)
do not know this one

>4. Run the sql
myQuery.ExecSQL

> and you can wrap the script up in a single transaction.
afraid I do not know this one either


as you can see, SQL is not my forte (at present). Any example would be
appreciated.

Pat

Roy,

<< I think its a limitation of V3 not being able to have a subselect in the
insert statement. >>

You are correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Pat


You must be currently selecting the master records for transfer using something like

INSERT INTO MasterArchive FROM (SELECT * FROM Master WHERE Field = Value)

so what you do is

Query.SQL.Add(SELECT JobID  FROM Master WHERE Field = Value)
Query.ExecSQL;
Query.First;
ListOfJobIDs:='';
while not Query.Eof do begin
ListOfJobIDs := ListOfJobIDs + Query.Fields[0].AsString +',';
Query.Next;
end;
Delete(ListOfJobIDs,Length(ListOfJobIDs),1);
Query.Close;
Query.SQL.Clear;
Query.SQL.Add('INSERT INTO MasterArchive SELECT * FROM Master WHERE JobID IN ('+LisOfJobIDs+');');
Query.SQL.Add('INSERT INTOTransactionArchives SELECT * FROM Transactions WHERE JobID IN ('+LisOfJobIDs+');');
Query.SQL.Add('DELETE FROM Master WHERE JobID IN ('+LisOfJobIDs+');');
Query.SQL.Add('DELETE FROM Transactions WHERE JobID IN ('+LisOfJobIDs+');');
Query.ExecSQL;


Totally untested obviously. You can then go on to wrap the last script in a transaction and rollback if there's a problem

Roy Lambert