To: "Jim McAtee" <[EMAIL PROTECTED]>
Cc: "MySQL List" <[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 8:28 AM
Subject: Re: copy data only from one table to another table
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?
That's exactly what I needed. Thank you for the detailed explanation. It makes perfect sense.
Jim
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
