I receive two data sets every month, one with 160 Columns of invoice data, one with 5 Columns of Forecast Data. 4/5 of the Forecast data columns match Columns in the invoice data (though the column names do not always match). One column is forecast volume which has no match in the invoice data.
I am trying to append these tables on top of each other, creating a new table with 161 Columns (the new column being forecast volume). Can I Union two tables in this way? perhaps by creating a new table with a null column and assigning the forecast volume to that null column?
Note: I tried a join based on the matching columns but this did not work because every material forecast is set to a specific date where we will order new material and there are therefore multiple forecast values for every single material code. In a join every invoice was then assigned either all the forecast volume or none.
2条答案
按热度按时间vmdwslir1#
If I understand correctly, yes you can.
btw column order and data types in both tables must match
gajydyqb2#
It is better for you to find a common field and join. If you can't, you can use union and cross join
If you want the table to be created in the following form, if it is empty in the equivalent of another table, we use union
cross apply
But if you want it to be as below and the columns of two tables are multiplied together, use Cross apply
Union all
Base Data:
1.invoice:
| Id | Name | family | T1 |
| ------------ | ------------ | ------------ | ------------ |
| 1 | aa | bb | 1 |
| 2 | asda | bdsdb | 3 |
| 3 | cc | tt | 12 |
2.forecast