How to copy a table without a primary key to another

Question

I want to copy one table to another.

For example, I have a Data(ID, time, serial_number...) table Data(ID, time, serial_number...) , and I have Temp_data(ID, time, serial_number...)

How to copy all elements without a primary key ( ID ).

I use MYSQL, I tried INSERT INTO Data SELECT * FROM Temp_data;

The problem is the primary key. both tables already have values, and I need everything to copy except ID.

Answer 1

Instead of SELECT * specify the necessary columns:

 INSERT INTO Data (time, serial_number...) SELECT (time, serial_number...) FROM Temp_data; 

Answer 2

You must specify the column name in the query, otherwise it will insert all columns

 INSERT INTO tbl2 (column1, column2) SELECT tbl1.col1, tbl1.col2 FROM tbl1 

Answer 3

This is a really annoying problem, because every time you need to update columns, you also need to update the code that copies. Here is the solution I use to get around the problem and check the code for the future:

 DROP TABLE IF EXISTS TempData; CREATE TEMPORARY TABLE TempData LIKE Data; INSERT INTO TempData SELECT * FROM Data; ALTER TABLE TempData CHANGE COLUMN 'id' 'id' INT(11) NULL, DROP PRIMARY KEY; UPDATE TempData SET id = 0; INSERT INTO Data SELECT * FROM TempData; DROP TABLE IF EXISTS TempData; 

Quickly, easily and once written, provided that the name of the source table is the same, it works every time.

Answer 4

Thanks Jonathan Nicole, plus a small correction, this answer works for MySQL:

DROP TABLE IF EXISTING TempData;

CREATE TEMPORARY TABLE TempData LIKE Data;

INSERT INTO TempData SELECT * FROM Data;

ALTER TABLE COLUMN CHANGE TempData id id INT (11) NULL;

UPDATE TempData SET id = null;

INSERT INTO Data SELECT * FROM TempData;

DROP TABLE IF EXISTING TempData;