Help: Joining two almost identical tables into one

[#1]

Hi guys,

Need some help with the below matter.

I want to merge the below two tables. They have common columns but would like to create a single table including all columns and data.

Script:

Initiatives_Risks_and_Issues:

LOAD [Initiative Number],
   [Risk/Issue ID],
[Risk Score] AS [Risk/Issue Risk Score],
[Initiative Risk/Issue] as [Risk/Issue],
[Risk/Issue Level],
[Risk/Issue Name],
[Risk/Issue Status],
[Risk/Issue Category],
[Risk/Issue Probability],
[Risk/Issue Impact],
[Risk/Issue Reporting Level],
[Risk/Issue Latest Update],
[Risk/Issue Impact Date],
[Risk/Issue Target Resolution Date],
[Risk/Issue Description],
[Risk/Issue Owner],
[Risk/Issue Priority],
[Risk/Issue Created Date],
[Risk/Issue Created By],
[Risk/Issue Last Updated Date],
[Risk/Issue Last Updated By],
[Risk/Issue Actual Resolution Date],
[Risk/Issue Resolution],
[Risk/Issue Impact Description]

FROM
[abc]
(ooxml, embedded labels, table is [Initiative Risks & Issues]);


Project_and_Programme_Risk_and_Issues:

LOAD [Project Number],
[Programme Number],
[Risk/Issue],
[Risk/Issue Level],
[Risk/Issue ID],
[Risk/Issue Name],
[Risk/Issue Status],
  [Risk/Issue Category],
[Risk/Issue Probability],
[Risk/Issue Impact],
[Risk/Issue Risk Score],
[Risk/Issue Reporting Level],
[Risk/Issue Latest Update],
[Risk/Issue Impact Date],
[Risk/Issue Target Resolution Date],
[Risk/Issue Description],
[Risk/Issue Owner],
[Risk/Issue Priority],
[Risk/Issue Created Date],
[Risk/Issue Created By],
[Risk/Issue Last Updated Date],
[Risk/Issue Last Updated By],
[Risk/Issue Actual Resolution Date],
[Risk/Issue Resolution],
[Risk/Issue Impact Description]

FROM
[abc]
(ooxml, embedded labels, table is [Risks & Issues]);

________________________________________________________________________________________________________________

I tried concatenating  the two tables

Concatenate (Initiatives_Risks_and_Issues) LOAD * RESIDENT Project_and_Programme_Risk_and_Issues;

DROP TABLE Initiatives_Risks_and_Issues;

but only give me the common columns.

Thanks!

[vishsaggi]

You have 24 field in your first table and 25 fields in your second table. Can you tell us what field is [Programme Number]

LOAD [Initiative Number],

--Here field name for Programme Number for the second table.
[Risk/Issue ID],
[Risk Score] AS [Risk/Issue Risk Score],
[Initiative Risk/Issue] as [Risk/Issue],
[Risk/Issue Level],
[Risk/Issue Name],
[Risk/Issue Status],
[Risk/Issue Category],
[Risk/Issue Probability],
[Risk/Issue Impact],
[Risk/Issue Reporting Level],
[Risk/Issue Latest Update],
[Risk/Issue Impact Date],
[Risk/Issue Target Resolution Date],
[Risk/Issue Description],
[Risk/Issue Owner],
[Risk/Issue Priority],
[Risk/Issue Created Date],
[Risk/Issue Created By],
[Risk/Issue Last Updated Date],
[Risk/Issue Last Updated By],
[Risk/Issue Actual Resolution Date],
[Risk/Issue Resolution],
[Risk/Issue Impact Description]

FROM
[abc]
(ooxml, embedded labels, table is [Initiative Risks & Issues]);

CONCATENATE
Project_and_Programme_Risk_and_Issues:
LOAD [Project Number] AS [Initiative Number],
[Programme Number] AS []
[Risk/Issue],
[Risk/Issue Level],
[Risk/Issue ID],
[Risk/Issue Name],
[Risk/Issue Status],
[Risk/Issue Category],
[Risk/Issue Probability],
[Risk/Issue Impact],
[Risk/Issue Risk Score],
[Risk/Issue Reporting Level],
[Risk/Issue Latest Update],
[Risk/Issue Impact Date],
[Risk/Issue Target Resolution Date],
[Risk/Issue Description],
[Risk/Issue Owner],
[Risk/Issue Priority],
[Risk/Issue Created Date],
[Risk/Issue Created By],
[Risk/Issue Last Updated Date],
[Risk/Issue Last Updated By],
[Risk/Issue Actual Resolution Date],
[Risk/Issue Resolution],
[Risk/Issue Impact Description]

FROM
[abc]
(ooxml, embedded labels, table is [Risks & Issues]);

[#3]

the end table should look like that:

Project Number],

[Initiative Number],
[Programme Number] ,
[Risk/Issue],
[Risk/Issue Level],
[Risk/Issue ID],
[Risk/Issue Name],
[Risk/Issue Status],
[Risk/Issue Category],
[Risk/Issue Probability],
[Risk/Issue Impact],
[Risk/Issue Risk Score],
[Risk/Issue Reporting Level],
[Risk/Issue Latest Update],
[Risk/Issue Impact Date],
[Risk/Issue Target Resolution Date],
[Risk/Issue Description],
[Risk/Issue Owner],
[Risk/Issue Priority],
[Risk/Issue Created Date],
[Risk/Issue Created By],
[Risk/Issue Last Updated Date],
[Risk/Issue Last Updated By],
[Risk/Issue Actual Resolution Date],
[Risk/Issue Resolution],
[Risk/Issue Impact Description]

one of the following 3 - Project Number, Initiative Number, Programme Number, is populated at all times.

Project Number, Programme Number and Initiative Number are keys.

Thanks