A semi-generic solution: You have tableA with columns (col1, col2, col3, ..., colN) where col1 is an auto_increment column. You have tableB that looks just like tableA except for some additional columns (extra1, extra2, col1, col2, col3, ..., colN, extra3, extra4). TableB is intended to act as an archive table for several tables of identically organized information.
for tableB the extra columns contain: extra1 - the name of the table where these rows are coming from extra2 - a new auto_increment id extra3 - a datetime column to show when the rows were copied. extra4 - initially null (BACKGROUND: This example is based on a situation that was once involved with. Each tableA contained billing information for different categories of clientel. The application they were designing was not responding quickly enough with one consolidated table of data so the decision was made to split it by category into separate tables. That decision improved their performance but created an administrative load they had not anticipated and they hired me to help work it out.) Now if you want to copy records from tableA to tableB you write an INSERT...SELECT... statement that would look like this: INSERT tableB (extra1, extra3, col1, col2, col3, ..., colN) SELECT 'tableA', NOW(), col1, col2, col3, ..., colN FROM tableA WHERE ....(some appropriate conditions)... Each value (column or literal) in the SELECT clause lines up one-to-one with each column listed in the INSERT () clause. A more trivial solution exists when the tables are virtually identical except you want the moved records to be autonumbered when they get to the new table. In this situation tableB has identical columns to tableA. For both tables col1 is an auto_increment column. To move records from tableA to tableB so that they get autonumbered by tableB you need to write a statement like this INSERT tableB (col2, col3, col4, ..., colN) SELECT col2, col3, col4, ..., colN FROM tableA WHERE ...( some appropriate conditions)... In both examples, we had to declare ALL of the source columns/values and the destination columns (cannot use *) because we needed to specifically exclude the auto_increment columns from the effects of the statement. Does that make sense or just make it worse? Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jim McAtee" <[EMAIL PROTECTED]> wrote on 11/17/2004 08:37:37 PM: > > ----- Original Message ----- > From: "Daniel Kasak" <[EMAIL PROTECTED]> > To: "Chip Wiegand" <[EMAIL PROTECTED]>; "MySQL List" > <[EMAIL PROTECTED]> > Sent: Wednesday, November 17, 2004 5:04 PM > Subject: Re: copy data only from one table to another table > > > > Chip Wiegand wrote: > > > >>How do I copy all data only from one table into another table? Both > >>tables > >>are in the same database. I have phpMyAdmin and it suppossedly does > >>this, > >>but it is not working, and there are no error messages. > >>Thanks, > >>-- > >>Chip > >> > >> > > create table table_2 > > select * from table_1 > > > How would this be done if table_2 already exists? It has an > auto_increment field as PK and I want to take all the rows from table_1 > and dump them into table_2. The records being copied from table_1 can get > new primary keys as there are no foreign key relationships to maintain. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
