Inserting and selecting data from a temp table

[joro]

Hi!

I have to submit a select statement that runs too long and there is no way to optimize it further.

So I rewrote the statement and execute it in 2 steps:
1. Insert data into temp table
2. select data from temp table, do some join and then use it in stimulsoft.

Basically what I am doing is (see attached screenshot):
Select * into #myNewTempTable from ( Select a,b,c,d,e from someTable inner join anotherTable inner join anothertable);

select * from #myNewTempTable
inner join A on A.a = #myNewTempTable.a
inner join B on B.b = #myNewTempTable.b
where #myNewTempTable.c = 2

-----

The problem is that I do not get any result rows. The temp table is populated correctly - I checked that.

I cannot "retrieve columns" because it returns an "unknown error". When I add the columns manually then they are not shown in "view datasource" (see attached screenshot)

Do you have any advice on how to solve this problem.


br
joro

[Edward]

Hi Joro,

In that case, the only thing which comes to my mind is to use the stored procedure, because the dictionary does not support multiple statements in SQL or 'multi-result sets'/'multi-select' type of queries.

Also, you could use a table - variable in your query which sometimes quicker and more flexible, but not always could be used and that could be a very 'debatable' topic:
https://stackoverflow.com/questions/118 ... sql-server

Thank you,
Edward