How to merge 2 tables to a 3rd new one

[ewigkeit]

Hello 🙂

I have a huge problem merging 2 table to a new one.

I came up to this code

CREATE TABLE tablenew SELECT * FROM (SELECT * FROM table1 UNION ALL  SELECT * FROM table2) AS tablenew

Problem is that it just puts one table unde the other and is not assigning new id's to the new table.
Example of id's

1
2
3
4
5
1
2
3

But i would like this :
1
2
3
4
5
6
7
8

Can anyone pls tell me how can i do it?
i know i must put in the query something like ON PRIMARY KEY UPDATE but i dont know the right syntax

[sfullman]

2 options:

1. on your UNION, you could instead select id + 10000, field1,field2, etc. FROM table2 where 10000 is a safe offset.

2. you could do:

   INSERT INTO newtable( field1, field2, field3, etc.)
   SELECT
   {your query here - but leave out the id}
   

the 2nd method is more elegant assuming you don't want' need key correspondence from either table