[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

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!
0
Saxitalis
Asked:
Saxitalis
3 Solutions
 
Chris MangusDatabase AdministratorCommented:
You could use a UNION if RecNum isn't important.

Try:

Select Type, Wt
From myTableA
Union
Select Type, Wt
From myTableB
Order By Type
Into TableC

Then, add your RecNum column back in as an Identity column with a seed of 1.
0
 
SQL_SERVER_DBACommented:
insert into table3(type, wt)
Select Type, Wt
From myTableA
Union
Select Type, Wt
From myTableB
0
 
imitchieCommented:
Is it possible for Tbl1 and Tbl2 to have the same Type?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
imitchieCommented:
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
0
 
Chris MangusDatabase AdministratorCommented:
Row_Number()!!  Very nice use of a new 2005 function, lmitchie!
0
 
SaxitalisAuthor Commented:
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.

0
 
imitchieCommented:
Does your new table have an identity column? If yes

Insert into table3(type, wt)
select Type, Sum(Wt) as Wt from
(
select Type, Wt from Tbl1
union all
select Type, Wt from Tbl2
) X
group by Type
order by Type

If it doesn't, then you need to include the RecNum column, i.e.

Insert into table3(RecNum, type, wt)
etc
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now