Solved

Union data from two tables into one SQL Server 2005

Posted on 2007-12-05
7
918 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

19 Experts available now in Live!

Get 1:1 Help Now