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
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
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?
Unimin Corporation - Spruce Pine
"Jim McAtee" <jmcatee@stripped> wrote on 11/17/2004 08:37:37 PM:
> ----- Original Message -----
> From: "Daniel Kasak" <dkasak@stripped>
> To: "Chip Wiegand" <chip.wiegand@stripped>; "MySQL List"
> 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
> >>are in the same database. I have phpMyAdmin and it suppossedly does
> >>but it is not working, and there are no error messages.
> > 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
> 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/mysql?unsub=1