0

I have two tables with same elements, the only differnce is there ids' which are primary key and auto increment.

Table1 | Table2
id1(PK)| id2(PK)
col1   | col1
col2   | col2
col3   | col3

I know some quick ways to do that like,

INSERT INTO table2 SELECT * FROM table1 where id1 = 2

while using such method the content of table2 has id2 = 2 as it copies all the fields directly to table2 from table1, to restric that, I can also use a method

INSERT INTO table2(col1,col2,col3) SELECT col1,col2,col3 FROM table1 WHERE id1 = 2

such way is good for short tables, but I have lot of columns in my table.

I need a quick way to copy all the columns from table1 to table2 leaving the primary columns which is id2, as it is autoincremented.

Its like I want to copy a specified row from table1 to table2 with different id2(which will be generated as its autoincremented).

Are there any possibilities.

K Cloud
  • 271
  • 1
  • 5
  • 15

2 Answers2

0

If you do not want to mention column names but want to copy all, then try copy all data into a temp table, then drop pk_id field, and then copy rest of the fields into desired table, lastly drop the temp table.

Refer to one of my answers to a similar queries: Mysql: Copy row but with new id

We can use temporary table to buffer first from main table and use it to copy to main table again. We can drop the pk field from the temp table and copy all other to the second table.

With reference to the answer by Tim Ruehsen in a referred posting: 

CREATE TEMPORARY TABLE tmp_table SELECT * from first_table WHERE ...;
ALTER TABLE tmp_table drop pk_id; # drop autoincrement field
# UPDATE tmp_table SET ...; # just needed to change other unique keys
INSERT INTO second_table SELECT 0, tmp_table.* FROM tmp_table;
DROP TABLE tmp_table;
Community
  • 1
  • 1
Ravinder Reddy
  • 23,692
  • 6
  • 52
  • 82
-1

Copy all rows from table1 except that have same id in table2

INSERT INTO table2(col1, col2, col3)
SELECT t1.col1, t1.col2, t1.col3    
FROM table1 t1
WHERE t1.id1 not in (SELECT id2 FROM table2);
bmanvelyan
  • 149
  • 4
  • first of all this is not what i'm doing, this will copy all the rows from table1 whose id do not match with id2, i strictly want to copy only one row, but do not want to copy the table1.id1 to the table2, the id2 will be generated automatically as it's autoincremented. – K Cloud Dec 19 '13 at 11:59