SQL append tables with exact same columns but different IDs

0
votes

Let us say I have thousands of tables with 1050 columns each, all of them in one DB. Is there a SQL command to append all the tables in one DB into one file, and erase the original tables (before append).

I.e.,

myDB contains table1, table2, table3 all of different number of rows, but same column names, same column types, and different unique IDs in the IDs column.

table1
myids,v1,v2,v3
id1,20.3,1.2,3.4
id10,2.1,5.2,9.3
id21,20.5,1.2,8.4
table2
myids,v1,v2,v3
id2,20.3,1.2,3.4
id92,2.1,5.2,9.3
table3
myids,v1,v2,v3
id3,1.3,2.2,5.4
id30,9.1,4.4,9.3

The real example is pretty much the same but with more columns and more rows.

Now I'd like my new table in the DB to be (also uniquely indexed):

mysupertable
myids,v1,v2,v3
id1,20.3,1.2,3.4
id10,2.1,5.2,9.3
id21,20.5,1.2,8.4
id2,20.3,1.2,3.4
id92,2.1,5.2,9.3
id3,1.3,2.2,5.4
id30,9.1,4.4,9.3

And I'd like table1, table2, table3 to be erased from the DB.

For reference, I am using SQLite3.

2
sqlsqlite

2 Answers

1
votes
insert into mysupertable (myids,v1,v2,v3)
select myids,v1,v2,v3 
from table1

over and over...

OR

insert into mysupertable (myids,v1,v2,v3)
select myids,v1,v2,v3 
from table1
union
select myids,v1,v2,v3 
from table2

etc.

1
votes
CREATE TABLE mysupertable AS
SELECT * FROM table1
UNION 
SELECT * FROM table2
UNION
SELECT * FROM table3;

DROP TABLE table1;
DROP TABLE table2;
DROP TABLE table3;

However, with thousands of tables, doing the union will be expensive. Better to use a series of INSERTs:

CREATE TABLE mysupertable AS
SELECT * FROM table1;
DROP TABLE table1;

INSERT INTO mysupertable
SELECT * FROM table2;
DROP TABLE table2;

INSERT INTO mysupertable
SELECT * FROM table3;
DROP TABLE table3;
...