MySQL - INSERT INTO Table With a New ID

Question

I have two tables and I want to copy a row from one to the other. However they both use an auto incrementing ID number as the primary key which is preventing me from copying a row if the ID already exists in the other table.

What I want to do is:

INSERT INTO tableB SELECT * FROM tableA WHERE ID = 1

What I was thinking might be possible would be to store the row as a variable, temporarily set the ID to be blank, and then it will be assigned the next highest number once inserted into the new table?

Is something like this possible?

Edit:

Columns

ID, Deal_ID, Redeemed_At, Wowcher_Code, Deal_Title, Customer_Name, House_Name_Number, Address_Line_1, Address_Line_2, City, County, Postcode, Email, Phone, Date_of_Birth, Custom_Field, Marketing_Permission, Product_Name, Product_Options

also have a look here: https://stackoverflow.com/questions/16108003/how-to-insert-values-into-auto-identity-column-in-mysql — Esteban P., Jun 19 '17 at 14:58

ScaisEdge · Accepted Answer · 2017-06-19T15:04:37.053

1

You cannot use * (all columns) in this case .. you must explicitally set the columns you need eg:

INSERT INTO tableB (col1, col2, col3) 
SELECT col1, col2, col3 
FROM tableA 
WHERE ID = 1

avoinding the ID columns

edited Jun 19 '17 at 15:04

answered Jun 19 '17 at 14:57

ScaisEdge

131,976
10
91
107

Perhaps you meant "cannot" rather than "can"? – Degan Jun 19 '17 at 15:04
Of all the answers this worked best as I needed to have the list of columns in brackets too. Thanks! – Edward1442 Jun 19 '17 at 15:06

score 1 · Answer 2 · answered Jun 19 '17 at 14:59

1

Just select the columns without the ID.

INSERT INTO tableB SELECT name,age,any_other_column FROM tableA WHERE ID = 1

answered Jun 19 '17 at 14:59

Rato Zumbi

63
1
7

score 0 · Answer 3 · answered Jun 19 '17 at 14:59

0

Try replace asterisks with fields list withouth ID

INSERT INTO tableB SELECT Field1,Field2,Field3...FieldXX FROM tableA WHERE ID = 1

answered Jun 19 '17 at 14:59

Stilet

92
2

MySQL - INSERT INTO Table With a New ID

3 Answers3