SQL Server SQL Append/Union with different Column Counts and Column Names

dy1byipe  于 2023-05-16  发布在  其他
关注(0)|答案(2)|浏览(157)

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.

vmdwslir

vmdwslir1#

If I understand correctly, yes you can.

SELECT col1, col2, col3, ..., col160, NULL AS forecast_volume
FROM invoice_data
UNION
SELECT col1, col2, col3, ..., col5, forecast_volume
FROM forecast_data;

btw column order and data types in both tables must match

gajydyqb

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

SELECT invoice.Id,Name,family,t1,null DataS FROM invoice 
UNION ALL
SELECT null Id,null  Name,null  family,null t1, DataS FROM forecast

cross apply

IdNamefamilyt1DataS
1aabb1aadd
2asdabdsdb3
3cctt12aadd
1aabb1assdsdda
2asdabdsdb3assdsdda
3cctt12assdsdda
1aabb1cdddc
2asdabdsdb3cdddc
3cctt12cdddc

But if you want it to be as below and the columns of two tables are multiplied together, use Cross apply

select invoice.Id,Name,family,t1,'' DataS from invoice 
cross apply( select * from forecast )a

Union all

IdNamefamilyt1DataS
1aabb1NULL
2asdabdsdb3NULL
3cctt12NULL
NULLNULLNULLNULLaadd
NULLNULLNULLNULLassdsdda
NULLNULLNULLNULLcdddc

Base Data:

1.invoice:
| Id | Name | family | T1 |
| ------------ | ------------ | ------------ | ------------ |
| 1 | aa | bb | 1 |
| 2 | asda | bdsdb | 3 |
| 3 | cc | tt | 12 |

2.forecast

IdDataS
1aadd
2assdsdda
3cdddc

相关问题