Select into without touching the autoincrement ID field

Question

I want to migrate data from one table to another without copying the autoincremented ID field.

source_table:

ID LABEL

-- -----

5 text

6 text2

dest_table:

ID LABEL

-- -----

SELECT LABEL
INTO dest_table
FROM source_table

Will the above statement work knowing that ID in dest_table is an auto-incremented primary key?

@user1336827 add it as an answer not as a comment so people can vote it and I can accept it — Adel Boutros, Jan 02 '14 at 21:52
You are almost better of creating the table and then loading it using INSERT INTO.... SELECT — logixologist, Jan 02 '14 at 21:55
If you would have pressed the F5 key you would have saved yourself a lot of time. — Zane, Jan 02 '14 at 22:27

Michael Mairegger · Accepted Answer · 2014-01-02T22:00:51.940

0

Yes, it will work ans result in

1 text

2 text2

But the problem is if you have a second table that is created and will referece to the created on. Then you have to leave the values of the primary keys as they are and not ignore them.

The query is not correct:

INSERT INTO destination (label) SELECT label FROM source

edited Jan 02 '14 at 22:00

answered Jan 02 '14 at 21:52

Michael Mairegger

6,833
28
41

But how does the query know that LABEL of source_table should fit into LABEL column of the dest_table? – Adel Boutros Jan 02 '14 at 21:53
By specifying the colums that you want to import – Michael Mairegger Jan 02 '14 at 22:52

score 0 · Answer 2 · answered Jan 02 '14 at 21:54

0

Yes it will work fine, the auto increment in the new table will just auto increment.

answered Jan 02 '14 at 21:54

user1336827

1,728
2
15
30

Select into without touching the autoincrement ID field

2 Answers2