Union data from two tables into one SQL Server 2005
Hello,
I have two Tables (or Views) with the same column headers populated with data that needs to be joined together into one table like this:
Tb1
RecNum Type Wt
1, A, 1.1
2, C, 2.2
3, E, 3.6
Tbl2
RecNum Type Wt
1, B, 5.1
2, D, 9.2
I am trying to get to a table that looks like this:
Tbl3
RecNum Type Wt
1, A, 1.1
2, B, 5.1
3, C, 2.2
4, D, 9.2
5, E, 3.6
Would you use a union stmnt somehow to do this?
Thanks!
I have two Tables (or Views) with the same column headers populated with data that needs to be joined together into one table like this:
Tb1
RecNum Type Wt
1, A, 1.1
2, C, 2.2
3, E, 3.6
Tbl2
RecNum Type Wt
1, B, 5.1
2, D, 9.2
I am trying to get to a table that looks like this:
Tbl3
RecNum Type Wt
1, A, 1.1
2, B, 5.1
3, C, 2.2
4, D, 9.2
5, E, 3.6
Would you use a union stmnt somehow to do this?
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
imitchie
Is it possible for Tbl1 and Tbl2 to have the same Type?
If not then
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
If yes then do you want to sum them?
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Sum(Wt) as Wt from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
group by Type
) Y
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
If yes then do you want to sum them?
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Sum(Wt) as Wt from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
group by Type
) Y
Row_Number()!! Very nice use of a new 2005 function, lmitchie!
ASKER
Yes there can be instances of the same type in both tables and they do need to be summed,
imitchie's code wortks well to sum and union all the data. Now to get it into a table, would I preface imithie's code with an insert Into statement?
Insert into table3(type, wt)
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Sum(Wt) as Wt from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
group by Type
) Y
When I try this I get:
Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
imitchie's code wortks well to sum and union all the data. Now to get it into a table, would I preface imithie's code with an insert Into statement?
Insert into table3(type, wt)
select Row_Number() over (Order by Type) as RecNum, * from
(
select Type, Sum(Wt) as Wt from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
group by Type
) Y
When I try this I get:
Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.