CODEWITHSUNDEEP
sqlsql-serverunion
Sinnerv
Sinnerv

Reputation: 263

Append tables in SQL

If I have the two following tables,

Table1

ItemNo  Desc    Order Number    Qty      S_Date     Location
AA       AA         AAA          A     AA/AA/AAAA     AAAA
BB       BB         BBB          B     BB/BB/BBBB     BBBB
CC       CC         CCC          C     CC/CC/CCCC     CCCC

Table 2

M_Order Item    M_Date      Total
XXX      X      XX/XX/XXXX   XX
YYY      Y      YY/YY/YYYY   YY

Can anyone advice me how to get the following table please.

Result Table

ItemNo  Desc    Order Number    Qty     S_Date    Location   M_Date     Total
AA       AA        AAA           A    AA/AA/AAAA    AAAA        
BB       BB        BBB           B    BB/BB/BBBB    BBBB        
CC       CC        CCC           C    CC/CC/CCCC    CCCC        
X       XXX                                                 XX/XX/XXXX    XX
Y       YYY                                                 YY/YY/YYYY    YY

Thanks

Upvotes: 1

Views: 316

Answers (3)

Veera
Veera

Reputation: 3492

Use the below query join those two tables.

I think you wish to combine the values of two [(ItemNo and Item) and (Desc and M_Desc)] columns in same column and all the other columns separately.

SELECT CAST(ItemNo AS VARCHAR(MAX)) AS ItemNo, CAST(Desc AS VARCHAR(MAX)) AS Desc, OrderNumber, Qty, 
       S_Date, Location, NULL AS M_Date, NULL AS Total 
FROM Table1
UNION ALL
SELECT CAST(Item AS VARCHAR(MAX)) AS ItemNo, CAST(M_Order AS VARCHAR(MAX)) AS Desc, NULL AS OrderNumber, NULL AS Qty, 
       NULL AS S_Date, NULL AS Location, M_Date, Total 
FROM Table2

Upvotes: 0

user4194400
user4194400

Reputation:

Similar to Gordons answer but the use of stars in SQL is one of the biggest NO NO's there is. Also for the actual join part itself you need to do tablename.column = tablename.column on the unique columns. 1 = 0 is a bit ambiguous. but you get the picture

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this with a full outer join trick:

select t1.*, t2.*
from table1 t1 full outer join
     table2 t2
     on 1 = 0;

This will give you the columns in both tables. Each row will be populated with values from only one of the tables.

Upvotes: 1

Related Questions