Need to move data from one table into another, using query

[NateofMT]

I need to copy a row, or multiple rows, from one table into another table and then delete it out of the first table, all based on the reults of a query.

Here is my sql statement for that query (named HasItBeenShippedQuery):

SELECT Job_Table.Ticket_Number, Job_Table.Ship_Sig
FROM Job_Table
WHERE (((Job_Table.Ship_Sig)<>""));

The name of the original table is Jobs_Table, the new table is Completed_Jobs_Table. The Completed_Jobs_Table will be appended to.

Will someone help me with the code to copy and delete? I am using a command button to run it and there will be no other user intervention.

Thanks,
Nateofmt

 

[anvil19]

Re: Need to move data from one table into another, using que

Hi NateofMT

For this to happen you need to separate the work into two operations.

Firstly, an INSERT INTO statement to add the records from table 1 into table2, like so:

INSERT INTO Completed_Jobs_Table
SELECT Job_Table.Ticket_Number, Job_Table.Ship_Sig 
FROM Job_Table 
WHERE (((Job_Table.Ship_Sig)<>""));

Of course here, the two tables will have the exact same structure(Number of fields, data types) or else you will need to add a VALUES ststement into the mix.

Next, we delete the rows from table1 like so:

DELETE (Job_Table.*) <-- Optional Extra if the records are all in one table
FROM Job_Table
WHERE (((Job_Table.Ship_Sig)<>""));

This DELETE statement will delete all the records where the WHERE is appropriate. As always, back up your table just in case it eliminates the incorrect data.


That should do it.

anvil19