SQL Query to move records from one table to another based on a third table

dstjohnjr
dstjohnjr used Ask the Experts™
on
I am looking to create a SQL Query to move records from one table to another table based on ID values inside of a third table (a cross reference table if you will... telling the query which records to move from Table 1 to Table 2)

I have three tables.

Table1 has the records I want to move records from.
Table2 has the records I want to move records to.
Table3 has the ID values of the records to be moved from Table1 to Table2.

All three tables have the same record ID identifier field, and for the sake of discussion here, let's call it RecordID.

TIA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
INSERT inti Table2
select * from Table1 where RecordID in (SELECT recordID from table3 )

Author

Commented:
Ok, I see by using the INSERT statement that it is effectively copying the records from Table1 to Table2.  To complete the move, would I not have to have a DELETE statement to remove the records from Table1?
You could do this and wrap everythig into a transaction. How many records are they?

BEGIN TRANSACTION
    INSERT inti Table2 
    select * from Table1 where RecordID in (SELECT recordID from table3 )

    DELETE FROM Table1 WHERE RecordID IN (SELECT RecordID From Table3)

    IF @@ERROR != 0
        GOTO BEDONE

ROLLBACK TRANSACTION

BEDONE:
    COMMIT TRANSACTION

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Arghh, small corrections
BEGIN TRANSACTION 
    INSERT INTO Table2  
    select * from Table1 where RecordID in (SELECT recordID from table3 ) 
 
    DELETE FROM Table1 WHERE RecordID IN (SELECT RecordID From Table3) 
 
    IF @@ERROR = 0 
        GOTO BEDONE 
 
ROLLBACK TRANSACTION 
 
BEDONE: 
    COMMIT TRANSACTION

Open in new window

Author

Commented:
There are about 150 some odd records...  I'll give this a shot.
You can safely use my version with the transaction wrapper. If something goes wrong, all changes will roll back.

Author

Commented:
This worked great. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial