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
artistdedigital
artistdedigital
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
Hi all,

I have two totally different tables with completely different data fields. Also, there is no common relationship between these two tables. However, I want to pick few data fields from the each table and merge into a new table! Is this even possible? If so how can I do that?

Thanks.
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/18/2014)
Hi all,

I have two totally different tables with completely different data fields. Also, there is no common relationship between these two tables. However, I want to pick few data fields from the each table and merge into a new table! Is this even possible? If so how can I do that?

Thanks.


Absolutely:

SELECT t1.Col1, t1.Col2, t2.Col3, t2.Col4
INTO Table3
FROM Table1 AS t1
, Table2 AS t2

It will create for you new table (Table3) as nice Cartesian product of Table1 and Table2.
Is it not what you rally looking after? Not surprised at all...
Please check the article from the link at the bottom of my signature. I hope it will help you to explain you issue in a way to attract more relevant answers.

_____________________________________________
"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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 8313
If you want a one-row-one-row combination you could use a cursor on each table and walk through the rows spitting out a new row with all of the relevant fields from each table. You could also do a row_number() on each set and join on that to get a one-to-one matchup. This is much better because not only is it not RBAR it also gives you at least some control about how to order the numbering.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
artistdedigital
artistdedigital
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
SELECT * FROM

(

SELECT datafields

FROM t1

UNION ALL

SELECT datafields FROM t2

) AS Anewtable WHERE t1.something = t2.something

Am I right here? I have to do the entire process in three tiers...almost 45 tables to around 20, then that 20 tables to 1 large table...

BTW, I am not actually creating new tables but more like create new views...the goal is to create one single sql query which we can run everyday to create one single table to pick datasets and run a report from...I hope its kind of clear now :-) any cool ideas to get this? thanks heaps...
stormsentinelcammy
stormsentinelcammy
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 807
I thought you first said that there is no relationships between either table...in any case the first suggestion is correct in that it will give you a cartesian product. If I were you I would see if there is any relationship you would derive from both tables because getting a cartesian product could lead to an immense number of rows.
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

...I hope its kind of clear now :-)
...


Not at all. In your first piost you stated that :

I have two totally different tables with completely different data fields.


Which means that you cannot use UNION, as it will require the same set of fields in both parts of union.
Using UNION you can select the common sub-set of the fields if there is one.

Also you said that:

Also, there is no common relationship between these two tables.


Which means that you cannot determine data from which row of Table2 should be selected for some row of Table1. So, the only reasonable suggestion is: you can built cartesian product - nothing else. (Actually, if youn number rows in both tables and join them based on this number, you will get nothing else other than subset of cartesion product, as you will still have no common relation between data of your two tables)

If you really want to get any relevant help, I strongly advised you to provide at least some small sample of your table DDL's and data, with explaining what results you would like to see from samples you posted.
Please read the forum etiquette article from the link at the bottom of my signature...

_____________________________________________
"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
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
You' d better be more specific. For example if you need all rows from T1 plus some rows of T2 which are some way related to T1 it may be type of

SELECT f1,..fn FROM T1
UNION ALL
SELECT g1, .. gn
FROM T2
WHERE EXISTS (SELECT 1 FROM T1 x WHERE x... = T2...)
artistdedigital
artistdedigital
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
Okay…sorry guys for making you all so confused w00t I really didn't mean it...

Here is an example –

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


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

Now, I want to merge R1,R2,R3,C1,C2,C3 in a new table called table.z

How can I do that? Sorry, if it’s a very lame question…Thanks to you all for your help :-)
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
Still wonder what you mean merge? Pls, provide sample src data and the result expected.
artistdedigital
artistdedigital
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
Merge means I want to get all the 6 columns(3 on each table) in to a new table...
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