SQLite Forum

How to create a table as the union of two tables?
Login

How to create a table as the union of two tables?

(1) By florence22 on 2021-06-06 13:55:34 [link]

I have two tables with the exact same columns (same order, same name, same data type)

I want to create a table as the union of these two tables.

I know I can show the result of the union with

```
SELECT * FROM tab1 UNION SELECT * FROM tab2;
```

But how to take the result into a new table?

This is what I came up with so far:

```
CREATE TABLE tempunion AS (SELECT * FROM tab1 WHERE 1=2);
INSERT INTO tempunion SELECT * FROM (SELECT * FROM tab1 UNION SELECT * FROM tab2);
```

Is there a better way to do this?

(2) By Keith Medcalf (kmedcalf) on 2021-06-06 15:15:24 in reply to 1 [link]

How about:

```
create table tempunion as select * from tab1;
insert into tempunion select * from tab2 except select * from tab1;
```

If there were no duplicates (that is, the operation is UNION ALL) then you can omit the `except select * from tab1`.

(3) By anonymous on 2021-06-06 15:56:56 in reply to 1

why not simply:

```
CREATE TABLE tempunion AS SELECT * FROM tab1 UNION SELECT * FROM tab2;
```

or have I overlooked a problem with that?

(4) By anonymous on 2021-06-07 20:20:11 in reply to 1 [link]

If you use `CREATE TABLE ... AS SELECT` then it only uses the column names and expression affinities of the result, and not any primary keys or any other things relevant to the original tables. Sometimes this is what is wanted, but sometimes it might not be.

If you want to make the new table with the same schema as the existing one, then one way might be to read the definition from the schema table, substitute a new table name, and then execute that SQL statement. That cannot be done purely in SQL, but it can be done in a C program.