Copy / move records between tables (A2K SR1)

Topic

New Reply

How does one copy/move one record from a linked table A to another linked table B using SQL? Both tables have the same structure.

Reply | Quote

Replies

Here is the SQL for copying the records

INSERT INTO TableB SELECT * FROM TableA;

Reply | Quote

(1) If you want to copy one specific record, you’ll have to add a WHERE condition to the SQL Bryan Carbonell posted. The precise form of the WHERE condition depends on how you identify the record to be copied.
For instance:

INSERT INTO TableB SELECT * FROM TableA WHERE RecordID=222;

(2) To move a record, copy it first and then delete it. This has to be done in two separate SQL statements.
For instance:

INSERT INTO TableB SELECT * FROM TableA WHERE RecordID=222;

DELETE FROM TableA WHERE RecordID=222;

Reply | Quote

Quite right about the WHERE.

I missed that when I read the post.

Sorry.

Reply | Quote

As HansV said, to move, Copy, then Delete.
I’d just add that, this is best done within a transaction. Otherwise if the Delete fails for any reason, you’ve got a mess with your data.

Regards,
jim

Reply | Quote