CODEWITHSUNDEEP
sqlsqlite
Andi
Andi

Reputation: 4865

Vertically concatenate columns from different tables in SQLite

I've got several tables, which share the same columns. I need to create a query/view where I vertically concatenate these columns. In addition, I need a second column that holds the name of the table where the particular value is coming from.

Here's my toy schema:

CREATE TABLE table_1 (ID INTEGER);
CREATE TABLE table_2 (ID INTEGER);

Let's assume the tables hold the following values:

table_1
ID
1
2
3

table_2
ID
4
5
2

My result should look like this:

table_result
ID     SourceTable
1      table_1
2      table_1
3      table_1
4      table_2
5      table_2
2      table_2

Upvotes: 1

Views: 849

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use union all:

select id, 'table_1' as sourcetable
from table_1
union all
select id, 'table_2' as sourcetable
from table_2;

Upvotes: 2

MatBailie
MatBailie

Reputation: 86715

SELECT ID, 'table_1' AS SourceTable FROM table_1
UNION ALL
SELECT ID, 'table_2' AS SourceTable FROM table_2

Note, this is preferred to just using UNION as that also removes duplicates. UNION ALL doesn't expend any resources trying to de-duplicate anything.

Upvotes: 1

Related Questions