You can translate the question and the replies:

Base view from query using temp tables (SQL server)

I can't seem to create a base view from a query that uses a temp table. From looking around online/on the forums, this seems like an intentional limitation due to the session-limited lifetime of temporary tables. However, this seems like a bad limitation. Base views allow retention of complex queries written in the 'source' SQL of the data source. In a lot of cases, these complex queries need to use temporary tables to solve performance issues, especially in cases where it's not possible or feasible to change the indexes of the data source tables. Of course temporary tables are session-limited, but the benefits are often significant and in some cases are the only ways for complex queries to be ported to Denodo. Is there any way to create a base view from a query that uses temporary tables? It's critical for data transforming/logic that takes magnitudes longer on the original tables. Temp tables allow custom indexes to optimise the exact logic needed in these queries, and not being able to do that in Denodo will be a serious issue.

2 Answers

Hi, I understand that you wish to create a base view in Denodo Virtual DataPort server over temporary tables created in Microsoft SQL server. I would like to let you know that Microsoft SQL server lets you create temporary tables with two different scope ranges as below: * ** Local temporary tables** are accessible exclusively within SQL Server Management Studio. * Whereas **global temporary tables** can be queried within and beyond the SQLserver management studio. Creating a base view in Denodo Virtual DataPort server can be achieved through the global temporary variables in SQL server. This is necessary because the scope of local temporary tables is restricted to the SQL Server Management Studio and cannot be expanded beyond it. You could also refer to the official [Microsoft documentation](,Temporary%20Tables,-Temporary%20tables%20are) for more information. Hope this helps!
Denodo Team
02-01-2024 07:14:03 -0500
I solved the issue. I needed to configure the source wrapper correctly to recognise keywords like 'DROP' and 'INTO'. ``` ALTER WRAPPER JDBC [[YOUR VIEW NAME]] SOURCECONFIGURATION ( delegatesqlsentenceassubquery = false, delegatesqlsentencevariablesasparameters = true ) ```
29-01-2024 21:02:11 -0500
You must sign in to add an answer. If you do not have an account, you can register here