Combining results from two tables into a unified table without common data

Question

I've read a bunch of examples on merging tables but most people have a joinable content. I do not have any data that can be joined upon except by using a FULL JOIN which leaves me multiple columns with the same name and NULLs where there is no data in the corresponding query.

CREATE TABLE #tmpBus
(
id varchar(50),
partnumber varchar(400),
attributecname nvarchar(255),
attributevalue nvarchar(1700),
assetfilename varchar(85),
orderby varchar(10)
)

CREATE TABLE #tmpBus1
(
id varchar(50),
name varchar(500),
url varchar(2550),
assetfilename varchar(850),
orderby varchar(10)
)

CREATE TABLE #tmpBus2
(
partnumber varchar(400),
attributecname varchar(255),
attributevale varchar(1700),
orderby varchar(10),
assetfilename varchar(850),
name varchar(500),
url varchar(2550)
)

INSERT INTO #tmpBus exec getSectionNewData 5665976;

INSERT INTO #tmpBus1 exec getSectionFeaturedData 5665976;

SELECT DISTINCT * FROM #tmpBus tb
FULL JOIN #tmpBus1 tb1
ON tb.partnumber=tb1.name

DROP TABLE #tmpBus
DROP TABLE #tmpBus1
DROP TABLE #tmpBus2

My goal is to get the data from the 2 temp tables (#tmpBus & #tmpBus1) I've created into one unified table (#tmpBus2). I tried every type of join and group by method I can think of. Here is a screenshot of the output of the above code. Notice the NULLs. I am trying to get a flat table into #tmpBus2 with the corresponding values from each table. So, the id,assetfilename and orderby fields would be unified instead of 2 separate columns and the ability to order by the orderby column.

Any help getting me headed in the right direction would be super helpful.

Answer 1

Use simple UNION join

SELECT id, partnernumber, attributecname, attributevalue, assetfilename, orderby, NULL AS url
FROM #tmpBus tb
UNION
SELECT id, name, NULL, NULL, assetfilename, orderby, url
FROM #tmpBus1 tb1
ORDER BY orderby