Merging 5 queries into 1

Question

I have the following code which I ran (see output in image below). And what I am trying to do is to merge all the 5 mysql_cteX tables, where X = 1, 2, 3, 4, 5 together into 1 full query table.

--------------------------------------
-- MERGING ALL QUERIES INTO 1 TABLE --
--------------------------------------
--Creating temp tables for common maturity TickerID data
declare @product nvarchar(max) = 'CCoal'
declare @product2 nvarchar(max)

set @mysql1 = '
if object_id(''tempdb..#mysql_cte1'') is not null drop table #mysql_cte1 
create table #mysql_cte1(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float, '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+' float,'+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' float)
select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte1 a left outer join #Product2_cte1 b 
on a.mtime = b.mtime
Order by DateTime DESC

'
set @mysql2 = '
if object_id(''tempdb..#mysql_cte2'') is not null drop table #mysql_cte2 
create table #mysql_cte2(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float, '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+' float,'+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' float)
select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte2 a left outer join #Product2_cte2 b 
on a.mtime = b.mtime
Order by DateTime DESC

'
set @mysql3 = '
if object_id(''tempdb..#mysql_cte3'') is not null drop table #mysql_cte3 
create table #mysql_cte3(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float, '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+' float,'+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' float)
select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte3 a left outer join #Product2_cte3 b 
on a.mtime = b.mtime
Order by DateTime DESC

'
set @mysql4 = '
if object_id(''tempdb..#mysql_cte4'') is not null drop table #mysql_cte4
create table #mysql_cte4(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float, '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+' float,'+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' float)
select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte4 a left outer join #Product2_cte4 b 
on a.mtime = b.mtime
Order by DateTime DESC

'
set @mysql5 = '
if object_id(''tempdb..#mysql_cte5'') is not null drop table #mysql_cte5 
create table #mysql_cte5(DateTime datetime,'+quotename(Concat(@product, ' ', 'TickerID'))+' int,'+quotename(Concat(@product2, ' ', 'TickerID'))+' int,'+quotename(Concat(@product, ' ', 'Bid'))+' float,'+quotename(Concat(@product, ' ', 'Ask'))+' float,'+quotename(Concat(@product2, ' ', 'Bid'))+' float,'+quotename(Concat(@product2, ' ', 'Ask'))+' float, '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+' float,'+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' float)
select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte5 a left outer join #Product2_cte5 b 
on a.mtime = b.mtime
Order by DateTime DESC

'

exec(@mysql1)
exec(@mysql2)
exec(@mysql3)
exec(@mysql4)
exec(@mysql5)
;

--Join the 5 merged queries together in parallel for comparison
set @Mysql = '
Select DateTime,'+quotename(Concat(@product, ' ', 'TickerID'))+','+quotename(Concat(@product2, ' ', 'TickerID'))+','+quotename(Concat(@product, ' ', 'Bid'))+','+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+','+quotename(Concat(@product2, ' ', 'Ask'))+','+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+','+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' from #mysql_cte1
UNION ALL
Select DateTime,'+quotename(Concat(@product, ' ', 'TickerID'))+','+quotename(Concat(@product2, ' ', 'TickerID'))+','+quotename(Concat(@product, ' ', 'Bid'))+','+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+','+quotename(Concat(@product2, ' ', 'Ask'))+','+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+','+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' from #mysql_cte2
UNION ALL
Select DateTime,'+quotename(Concat(@product, ' ', 'TickerID'))+','+quotename(Concat(@product2, ' ', 'TickerID'))+','+quotename(Concat(@product, ' ', 'Bid'))+','+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+','+quotename(Concat(@product2, ' ', 'Ask'))+','+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+','+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' from #mysql_cte3
UNION ALL
Select DateTime,'+quotename(Concat(@product, ' ', 'TickerID'))+','+quotename(Concat(@product2, ' ', 'TickerID'))+','+quotename(Concat(@product, ' ', 'Bid'))+','+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+','+quotename(Concat(@product2, ' ', 'Ask'))+','+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+','+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' from #mysql_cte4
UNION ALL
Select DateTime,'+quotename(Concat(@product, ' ', 'TickerID'))+','+quotename(Concat(@product2, ' ', 'TickerID'))+','+quotename(Concat(@product, ' ', 'Bid'))+','+quotename(Concat(@product, ' ', 'Ask'))+','+quotename(Concat(@product2, ' ', 'Bid'))+','+quotename(Concat(@product2, ' ', 'Ask'))+','+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+','+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+' from #mysql_cte5
Order by DateTime DESC
'

exec (@Mysql)

However, the output I get are these, and not the 1 full merged table:

Please assist, I am not too sure what I did wrong. The error message I got was: Msg 208, Level 16, State 0, Line 25 Invalid object name '#mysql_cte1'.

You are creating temporary tables but not inserting into them. Just calling select will not insert data into them but just display them as you are seeing. [Insert example](https://www.tutorialspoint.com/sql/sql-insert-query.htm) — Ranjeet, Jul 27 '17 at 06:07
Does this answer your question? [MySQL - Selecting data from multiple tables all with same structure but different data](/q/409705/90527), [SQL query return data from multiple tables](/q/12475850/90527) — outis, Oct 19 '22 at 08:25

score 1 · Accepted Answer · answered Jul 27 '17 at 06:16

If all the tables have the same number of columns and correspondant data type

you could use union for "merge " the table (eg for the first 3 table) ,, UNION ALL merge all rows .. UNION merge distinct rows

   select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte1 a left outer join #Product2_cte1 b 
on a.mtime = b.mtime


UNION ALL 

select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte2 a left outer join #Product2_cte2 b 
on a.mtime = b.mtime


UNION ALL 

select a.mtime as DateTime,
    a.tickerid as '+quotename(Concat(@product, ' ', 'TickerID'))+',
    b.tickerid as '+quotename(Concat(@product2, ' ', 'TickerID'))+',
    a.bid as '+quotename(Concat(@product, ' ', 'Bid'))+',
    a.ask as '+quotename(Concat(@product, ' ', 'Ask'))+',
    b.bid as '+quotename(Concat(@product2, ' ', 'Bid'))+',
    b.ask as '+quotename(Concat(@product2, ' ', 'Ask'))+',
    ISNULL(a.bid, 0) - ISNULL(b.ask, 0) as '+quotename(Concat(@product, ' ', 'Bid - ', @product2, ' ','Ask'))+',
    ISNULL(a.ask, 0) - ISNULL(b.bid, 0) as '+quotename(Concat(@product, ' ', 'Ask - ', @product2, ' ','Bid'))+'
from #Product_cte3 a left outer join #Product2_cte3 b 
on a.mtime = b.mtime
Order by DateTime DESC

Merging 5 queries into 1

1 Answers1