Re: copy data only from one table to another table
On Wed, 17 Nov 2004 18:37:37 -0700, Jim McAtee [EMAIL PROTECTED] wrote: 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. I suppose the following (provided the table structure of the both tables are exactly the same): first, make a backup ;) then, delete the primary key column of the table to be imported (for correct new primary key values later) create a table dump. do NOT make use of extended inserts, and be sure not to include the table creation commands. only the data itself is needed. Then import the data into the second table and be happy :) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copy data only from one table to another table
[snip] 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. [/snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Chip Wiegand [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 8:10 AM Subject: RE: copy data only from one table to another table [snip] 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. [/snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copy data only from one table to another table
[snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:19 AM Subject: RE: copy data only from one table to another table [snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. Well, never mind then. I thought only apostrophes were valid. -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copy data only from one table to another table
[snip] [snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. Well, never mind then. I thought only apostrophes were valid. [/snip] You would use apostrophes around the data itself. Consider this... INSERT INTO `table2` (`columnA2`, `columnB2`) SELECT `columnA1`, `columnB1` FROM `table1` WHERE `columnA1` = 'this data' AND `columnB1` = 'that data' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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? 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]
Re: copy data only from one table to another table
- Original Message - From: [EMAIL PROTECTED] 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]
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 -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- 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]
