How can I merge two or more table into a single table with all columns name of all tables

Question

How can I merge two or more table into a single table with all columns name of all tables This is table1

What's the connection between the two tables? Putting them together would require some connection, some relationship between them. In your third picture, it's not clear there is one. — Mike M, Feb 28 '18 at 07:51
Welcome to Stack Overflow! You seem to be asking for someone to write some code for you. Stack Overflow is a question and answer site, not a code-writing service. Please see here https://stackoverflow.com/help/how-to-ask to learn how to write effective questions. — Yogesh Sharma, Feb 28 '18 at 07:53
I'm voting to close this question as off-topic because no attempt was made. — Yogesh Sharma, Feb 28 '18 at 07:55
Possible duplicate of [Insert results of a stored procedure into a temporary table](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — Yogendra, Feb 28 '18 at 10:29

score 2 · Accepted Answer · answered Feb 28 '18 at 08:16

To join tables in this way, you have to have additional column to join on, one of possibilities is to use ROW_NUMBER():

select T1.[date] [Table1_date],
       T1.[als] [Table1_als],
       T1.[zxc] [Table1_zxc],
       T2.[date] [Table2_date],
       T2.[bls] [Table2_bls],
       T2.[zxc] [Table2_zxc]
from (
    select row_number() over (order by [date]) [rn],[date],[als],[zxc] from Table1
) [T1] left /*right - depends which table has more rows*/ join (
    select row_number() over (order by [date]) [rn],[date],[bls],[zxc] from Table2
) [T2] on T1.[rn] = T2.[rn]

In order to join multiple tables (>2), first table to occure in query should be one with maximum amount of records. Then you can use series of left join with the rest of tables, joining on column generated by row_number().

How can I merge two or more table into a single table with all columns name of all tables

1 Answers1