CODEWITHSUNDEEP
sql
user422318
user422318

Reputation: 321

Combining two tables using UNION ALL

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?

Upvotes: 3

Views: 16182

Answers (2)

pf1957
pf1957

Reputation: 1007

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?

Upvotes: 4

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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

Upvotes: 7

Related Questions