Help copying data from one mysql table to another plz!

PHP
1

I’m tyring to set up a script which will copy data from one MYSQL table to another. Both tables are within the same database.
The first table has around 30 columns in it whereas the second table only has 3.
Both tables will have an Id column which will hold a unique value for each row.
I need to create a script which will get all of the data from the first database based on a WHERE clause.
The script then needs to check the second database to see if a row with the unique id already exists in the second table if it does already exist I need the script to disregard that row of data.
If it doesn’t already exist in the second table I need to then insert this new row into the table.

Can anyone help me out with the above please? Are there any scripts / tutorials that I can look at that anyone already knows about?

Thanks for the help in advance.

2
  1. Put an unique index on the id column of the second table (that id column must not be autoincremental)
  2. Use INSERT IGNORE … SELECT … to copy the rows from the first to the second table

INSERT IGNORE
INTO table2
SELECT 
    id
  , column1
  , column2
FROM table1
WHERE ....

The IGNORE means the INSERT will discard any rows that cause a duplicate key error. So putting a unique index on the id column makes sure you won’t insert rows with an id that already exists in table2.

3

Fantastic!! Thanks for the quick reply Guido. I’ll give it a go and let you know how I get on.

4

I’m trying to set up the script using INSERT IGNORE but it’s not working. I keep getting a column count doesn’t match error.
Here’s the code I have on my php page. Can you tell me where I’m going wrong please?

$query = "INSERT IGNORE Used_Notes SELECT Feed_Id, Full_Registration FROM Used_Stock WHERE Feed_Id='11102' AND Model LIKE '%M3%' OR Feed_Id='11102' AND Model LIKE '%M5%' OR Feed_Id='11102' AND Model LIKE '%M6%' OR Feed_Id='11102' AND Model LIKE '%Z4 M%' OR Feed_Id='11102' AND Model LIKE '%X5 M%' OR Feed_Id='11102' AND Model LIKE '%X6 M%'" ;
$result = mysql_query($query) or die(mysql_error());
5

You said table 2 has 3 columns, but in your INSERT you only have two.

6

Table 2 does have 3 columns but I only take two of the columns form table 1 to populate two of the columns. The other one is done by manual input.
Is there a way I can change the script to accomodate this?

Thanks for your help.

7

Manual?
If this means that column gets its value later, just put ‘’, 0, NULL or whatever value the column should get until that manual input, in the right place in the SELECT.
For example, if the “manual” column is the second column of the table, change the query to:


INSERT IGNORE Used_Notes 
SELECT 
    Feed_Id
  , NULL
  , Full_Registration 
FROM Used_Stock 
WHERE 
  ...

I used NULL here, just change it into whatever value you want the column to have.