Copy row from table to table

On Tue, Dec 14, 2004 at 11:48:21AM -0600, Kall, Bruce A. wrote:

What is the easiest way to copy a particular row from one table to
another (assuming all the columns are exactly the same). I want to
maintain a table ('backup_table') that has rows I have (or will be)
deleting from my 'original_table'. (Both tables have the same 68 columns).

I've tried

INSERT INTO backup_table SELECT * FROM original_table WHERE id='2305'

That should work if the table definitions are identical or compatible,
including column positions being the same. Didn't it work? If not,
what happened?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> 12/14/04 1:45 PM >>>

On Tue, Dec 14, 2004 at 11:48:21AM -0600, Kall, Bruce A. wrote:

What is the easiest way to copy a particular row from one table to
another (assuming all the columns are exactly the same). I want to

maintain a table ('backup_table') that has rows I have (or will be)

deleting from my 'original_table'. (Both tables have the same 68

columns).

I've tried

INSERT INTO backup_table SELECT * FROM original_table WHERE

id='2305'

That should work if the table definitions are identical or

compatible,

including column positions being the same. Didn't it work? If not,
what happened?

One cute way to guarantee that the table definitions are identical is:

CREATE TABLE backup_table AS SELECT * FROM original_table WHERE 1=0;

That ensures that subsequently doing the following is safe:

INSERT INTO backup_table SELECT * FROM original_table WHERE id='2305';