Solved

Union data from two tables into one SQL Server 2005

Posted on 2007-12-05
7
919 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
7 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 100 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 200 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 200 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now