List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 18 2004 3:28pm
Subject:Re: copy data only from one table to another table
View as plain text  
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" <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" 
> <mysql@stripped>
> 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/mysql?unsub=1
> 

Thread
copy data only from one table to another tableChip Wiegand18 Nov
  • Re: copy data only from one table to another tableDaniel Kasak18 Nov
  • Re: copy data only from one table to another tableJim McAtee18 Nov
    • Re: copy data only from one table to another tableFrederic Wenzel18 Nov
    • Re: copy data only from one table to another tableSGreen18 Nov
      • Re: copy data only from one table to another tableJim McAtee18 Nov
RE: copy data only from one table to another tableJay Blanchard18 Nov
  • Re: copy data only from one table to another tableJonathan Mangin18 Nov
  • 2 ways Replication in MySQLMojtaba Faridzad18 Nov
    • Re: 2 ways Replication in MySQLAlec.Cawley18 Nov
      • Re: 2 ways Replication in MySQLMojtaba Faridzad18 Nov
RE: copy data only from one table to another tableJay Blanchard18 Nov
  • Re: copy data only from one table to another tableJonathan Mangin18 Nov
RE: 2 ways Replication in MySQLKevin Cowley18 Nov
RE: copy data only from one table to another tableJay Blanchard18 Nov
Re: 2 ways Replication in MySQLMojtaba Faridzad18 Nov