CODEWITHSUNDEEP
sqlpostgresql
Tusharshar
Tusharshar

Reputation: 107

Appending two tables in SQL

I have two tables and I want to append them into a new table. Let me illustrate with an example.

Table A : (A1,A2,A3,A4)
Table B : (A1,A2,B1,B2,B3)
Merged Table : (A1,A2,A3,A4,B1,B2,B3)

I don't want to join the tables just want to append their rows.So if the table A has an entry with (1,2,3,4) and table B have an entry (1,2,7,8,9). The resultant table will have two rows

(1,2,3,4,NULL,NULL,NULL)
(1,2,NULL,NULL,7,8,9)

Currently I am doing it in a laboursome way; creating a table having distinct columns from both table and then inserting the required columns from both the tables. This works but is terribly messy and prone to errors.

is there any way to do this other than this process? The number of columns is quite large too.

Upvotes: 0

Views: 598

Answers (2)

void
void

Reputation: 7890

you need an inner join with a union:

SELECT   A.A1, A.A2, A.A3,A.A4, NULL, NULL, NULL
From     TableA A 
JOIN     TableB B ON (A.A1=B.A1 AND A.A2=B.A2)
UNION
SELECT   B.A1, B.A2, NULL, NULL, B.B1, B.B2, B.B3
From     TableA A 
JOIN     TableB B ON (A.A1=B.A1 AND A.A2=B.A2)

Upvotes: 1

jarlh
jarlh

Reputation: 44776

It's a little bit unclear what you mean with "having distinct columns", but this is what I guess you want:

insert into tableC
select a1, a2, a3, a4, NULL, NULL, NULL from tableA
union all
select a1, a2, NULL, NULL, b1, b2, b3 from tableB

I.e. insert into the new table (above called tableC), all rows from tableA and all rows from tableB.

Upvotes: 0

Related Questions