Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Merge two tables into one.


Merge two tables into one.

Author
Message
serg-52
serg-52
Right there with Babe
Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)Right there with Babe (798 reputation)

Group: General Forum Members
Points: 798 Visits: 1814
Then JOIN them

select x.R1,x.R2,x.R3, y.C1,y.C2,y.C3
from Database.table1 as x
join Database.table2 as y

to get all possible combinations aka cartesian product.
Restrict combinations using WHERE clause

http://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
artistdedigital (2/20/2014)
Merge means I want to get all the 6 columns(3 on each table) in to a new table...



Select R1,R2,R3
From Database.table1 as table.x


Select C1,C2,C3
From Database.table2 as table.y


It may surprise you, but tables are not just set of columns...
Your select will or may return some rows!

What about if select from your table Database.table1 returns as twice as many rows as select from Database.table2?

That do you execpt to be result of the merge? That is why I have asked you to provide the example of expected results based on a sample of data you have. Until you do that, you are no going to get relevant help, as it's imposible to see what is inside of your head (I've lost my crystal ball to do so)...

The maximum what I can tell you right now, that to have resultset containig 6 columns from the both selects you have you need to JOIN your table somehow. If there is nothing links rows of these two tables, then you have two options: 1. Cartesian product and 2. UNION ALL.

You already been shown examples of option #1:
To do union in your case you can try this:

INSERT [YourNewTable]
SELECT R1,R2,R3, NULL, NULL, NULL
FROM Database.table1 as table.x
UNION ALL
Select NULL, NULL, NULL, C1, C2, C3
FROM Database.table2 as table.y

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
serg-52 (2/20/2014)
Then JOIN them

select x.R1,x.R2,x.R3, y.C1,y.C2,y.C3
from Database.table1 as x
join Database.table2 as y

to get all possible combinations aka cartesian product.
Restrict combinations using WHERE clause

http://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx


On its own, join will raise an error.

select x.R1,x.R2,x.R3, y.C1,y.C2,y.C3
from Database.table1 as x
CROSS JOIN Database.table2 as y

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Sushil Dwivedi
Sushil Dwivedi
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 142
You can use Merge join available in SSIS package
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search