Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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