?
Solved

Union data from two tables into one SQL Server 2005

Posted on 2007-12-05
7
Medium Priority
?
925 Views
Last Modified: 2008-02-01
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
Comment
Question by:Saxitalis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 400 total points
ID: 20413328
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
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 800 total points
ID: 20415352
insert into table3(type, wt)
Select Type, Wt
From myTableA
Union
Select Type, Wt
From myTableB
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20416118
Is it possible for Tbl1 and Tbl2 to have the same Type?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 25

Expert Comment

by:imitchie
ID: 20416140
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
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20416186
Row_Number()!!  Very nice use of a new 2005 function, lmitchie!
0
 

Author Comment

by:Saxitalis
ID: 20416805
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 800 total points
ID: 20416814
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question