SQL Merge multiple tables with different columns into one

Question

I need to create one master table from 5 tables, the difficulty is that the same column across the tables may have a different name. so for instance

For simplicity I`m just going to give an example for 2 tables

+----+----+
| 1  | 2  |
+----+----+
| PO | P  |
| VE | V  |
| TE | TE |
| LO | LO |
| IN |    |
| D  |    |
| X  |    |
| Y  |    |
|    | A  |
|    | B  |
|    | C  |
+----+----+

so as you can see PO doesn`t have the same column name as the corresponding value in table 2 yet they are the same record. I need to aggregate these 2 tables into one master.

What I did was began with the table that has the most repeated columns and I am trying to merge the other tables into it. When there is a column only found on one table I want the other fields to display null. Also I don't want any duplicates. Hope someone can help me out!

Cheers

The list of letters you posted doesn't make any sense. Please provide the table strucutre and your expected result. — Lmu92
– Lmu92, Commented Aug 11, 2014 at 16:16
My apologies, kind of new to posting here. long time viewer! and by variables i did mean column names. — midiman
– midiman, Commented Aug 11, 2014 at 16:24

Solkar · Accepted Answer · 2014-08-11 17:48:16Z

1

yet they are the same record.

No, they are not.

They could, however, represent different views of the same business entities. To "merge" them you must first specify what the JOIN criterion between them shall be.

Given it is

one.PO = two.P.

Then you must write a SQL statement like

SELECT one.PO AS ID, 
       one.VE, 
       /*same for TE, LO, IN, D, X, Y, */ 
       two.A, 
       two.B, 
       two.C
  INTO t_what_the_frak_the_new_table_shall_be_called
  FROM t_what_the_frak_table_1_is_called AS one,
       JOIN t_what_the_frak_table_2_is_called AS two 
       ON one.PO = two.P;
GO

edited Aug 11, 2014 at 17:48

answered Aug 11, 2014 at 17:38

Solkar

1,23612 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

midiman Over a year ago

why did you set one.PO as ID?

midiman Over a year ago

is there a way to insert all fields without actually referencing them (you commented that i need to do it for all columns)

Solkar Over a year ago

FirstQuestion: Because you don't us details about your data structure, thus I have to guess. Second: Sort of. But that would be way beyond the SQL skill you have yet demonstrated here.

midiman Over a year ago

I get that, but I mean what was the point of setting it as ID? you never used "ID" again

Solkar Over a year ago

Would it become clearer if I call it "foobar" instead?

|

Collectives™ on Stack Overflow

SQL Merge multiple tables with different columns into one

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related