I have two tables one contains an ID, the other one contains HourlyRate and Bonus, I want to combine them the following way
| ID | HourlyRate | Bonus |
|---|---|---|
| 1 | 10.50 | 3 |
| 2 | 7.85 | 2 |
| 3 | 30.10 | 4 |
But I only get something like that by using merge, union, etc
| ID | HourlyRate | Bonus |
|---|---|---|
| NULL | 10.50 | 3 |
| NULL | 7.85 | 2 |
| NULL | 30.10 | 4 |
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
From your question, you are looking to perform a join operation between both tables. But the bad thing is that joining requires to be performed over one or many columns in each table.
Besides, what is the logic behind the join operation you are trying to perform? How the row with ID = 1 in the first table is combined with the row having Hourly Rate = 10.50?
First step: Adding a join column
To solve this issue, you have to generate a new column that can be used in the join (merge join). As an example, if you need to join using the row number; the first line in the first table is combined with the first line in the second table.... Then, you need to add a row number column for each table.
You can add a row number column for each table using a Script Component as explained in the following articles:
Remember that you need two Script Component in your case if the ID column in the first table may contain gaps between numbers (i.e. 1,2,3,6,7,...).
Another approach is to add a row number column from SQL (if the data is stored in SQL) is using ROW_NUMBER() SQL function:
SELECT ROW_NUMBER() OVER(ORDER BY (Select 0)) as row_id, *
FROM TableA
More details in :
Second step: Joining both data sources
After adding a row number column, you should use a merge join component to combine both data sources. You can learn more about the merge and merge join components in the following articles: