Combining two tables using UNION ALL

Question

I have two tables with the same fields. I wish to combine these into one big table. Each table's primary key is a unique respondent id.

When I combine the two tables with a UNION ALL, some data gets put into the wrong fields! Why is this? They are in the correct fields in the original tables. Is UNION ALL the correct operator?

Answer 1

The thing to remember about Unions is that the column names for the resulting dataset will be defined by the first table in the union declaration.

Additionally, the order they are found in the declaration for the structure of the table will be impactive in this.

if tableA is defined as Name, Address, Email while table B is defined as Name, Email, Address

select * from tableA
union all
select * from tableB

will put tableB.Email in a column aliased as 'Address' to correct this, you must define the columns in your select statement:

select Name, Email, Address from tableA
union all
select Name, Email Address from tableB

Answer 2

UNION ALL should be the right operation, but did you list fields explicitely by name in both parts or did you use asterisk as wildcard?