u-sqlrowset

merging 2 unequal column size rowsets in u sql

I have a rowsetA with 3 columns. I need to add this rowsetA to an existing rowsetB which has the above 3 columns as well as other columns. How can I add/union above 2 rowsets such that rowsetA will have null/empty/default values for other columns present in rowsetB?

Solution

  • The easiest way is to add default null values in rowsetA when doing UNION with rowsetB. 

    @rowsetA = EXTRACT A string,
B string,
C string 
FROM @path 
USING Extractors.Csv();
@rowsetB = EXTRACT A string,
B string,
C string,
D string,
E string
FROM @path1
USING Extractors.Csv();
@union = SELECT A,B,C,null AS D,null AS E FROM @rowsetA
UNION
SELECT A,B,C,D,E FROM @rowsetB;

    This way you will have null value on missing columns. Note for other data types such as DateTime,int,etc, you just put default(int?) instead of null.

    Hope this helps