CODEWITHSUNDEEP
mysql
Rojer Tenison
Rojer Tenison

Reputation: 49

Copy Data From One Database To Another In Same Table

I want to copy data from one database which has table named domains to another database which also has the table named domains to it.

I have tried doing it using phpmyadmin but it doesn't copy maybe because of Auto_increment values. it just doesn't get copied to another database.table.

I want to know what can be done in this regard? also I don't want to copy old ID(auto_increment) values from first database to another.

phpmyadmin response.

 #1136 - Column count doesn't match value count at row 1

both the structures are same in the databases still this.

My Query.

 INSERT INTO `site1`.`domains`
 SELECT * FROM `site33`.`domains`

^ This much is fixed. now comes the auto_increment problem I Get:

  #1062 - Duplicate entry '1' for key 'PRIMARY'

Upvotes: 2

Views: 13477

Answers (2)

heretolearn
heretolearn

Reputation: 6545

You could do something like this:

INSERT INTO database2.table1 (field2,field3)
SELECT table2.field2,table2.field3
FROM table2;

Note: Do not include the column which is auto increment in your insert and select statement.

Sql Fiddle example

Upvotes: 9

GolezTrol
GolezTrol

Reputation: 116100

You are using an insert statement that has an incorrect number of values, for instance:

for a table with columns a, b and c, these are all invalid:

INSERT INTO YourTable VALUES (1, 2);

INSERT INTO YourTable(b, c) VALUES (1, 2, 3);

INSERT INTO YourTable(a, b, c) VALUES (2, 3);

The number of columns in the column list must match the number of values in the value list.

You may only omit the column list, but only if you specify one value for each column and in the order in which the columns exist in the table. This is bad practice, though. It is better to always specify the exact columns you need.

Upvotes: 1

Related Questions