Thread: Copy row from table to table

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'

COPY seems to only work between tables and files.

Thanks,
Bruce

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';