Question
Evonsys
US
Last activity: 23 Oct 2019 9:19 EDT
Temp tables in sql query component
Hi All,
We have a big query to be executed this query needs to be executed in 2 steps in MS sql server
first step will be getting some data by using other physical tables and placing them in the Temp table, and after this the second step will use the data in the temp table.
so we used two sql command executes first one will do the temp table query execution and the second will do the second query execution from temp table,
but by the time the second query gets executed the temp table is not available to the second query thread, we googled and found that the temp tables will be cleared immediately when the current connection is closed.
we also tried keeping both the queries in one single sql command, but when we use this approach the controller is not coming out of the execute method.
could some one please help us on this.
we tried
Accepted Solution
Evonsys
US
We achieved this by executing both the queries by using single query command, and the queries are seperated by semicolon.
Pegasystems Inc.
US
I can't really offer any suggestions on your actual SQL. If your statement is not returning when you execute in one call, then is it possible it is timing out? You can increase the CommandTimeout to allow more time to execute. If you cannot improve your SQL, you could perform the second query in your automation via code. The first query, instead of writing to a temp table, could instead return the rows. You could then filter and effectively query those rows in code in the automation.
Accepted Solution
Evonsys
US
We achieved this by executing both the queries by using single query command, and the queries are seperated by semicolon.