Обсуждение: 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';