Using temporary tables in SQL query recipes

Highlighted
antonstam
Level 2
Using temporary tables in SQL query recipes
Jump to solution

I'm struggling with using temp tables in a SQL query recipe (script recipes work fine, but I'd prefer to use queries). According to the docs, query recipes support "Pre statements that create temporary tables, used by the main SELECT", but I can't figure out how.

My best attempt looks like this:

SELECT *
INTO #temptable
FROM sys.tables

-- DKU_END_STATEMENT

SELECT *
FROM #temptable

I'm using Microsoft SQL Server. I see no reason why this query shouldn't work, but Dataiku throws the error that 'Query does not return results'. How could I get this query to work without errors?

0 Kudos
1 Solution

Accepted Solutions
Highlighted
fchataigner2 Dataiker
Dataiker
Re: Using temporary tables in SQL query recipes
Jump to solution

Hi,

the cause is the use of the `SELECT ... INTO ...` syntax, to create the temporary table. During the validation, DSS won't play any DDL statements, only DML ones, so this exclude CTAS constructs like `SELECT ... INTO ...`. Since the first part of the query is not run, the `#temptable` doesn't exist when DSS considers the `SELECT * FROM #temptable` to infer the output schema.

You need to rewrite the query using `CREATE TABLE` and `INSERT INTO`.

Regards,

    Frederic

ps: you don't need the `-- DKU_END_STATEMENT`, and can use semi-colons to mark the end of a statement

 

View solution in original post

0 Kudos
2 Replies
Highlighted
fchataigner2 Dataiker
Dataiker
Re: Using temporary tables in SQL query recipes
Jump to solution

Hi,

the cause is the use of the `SELECT ... INTO ...` syntax, to create the temporary table. During the validation, DSS won't play any DDL statements, only DML ones, so this exclude CTAS constructs like `SELECT ... INTO ...`. Since the first part of the query is not run, the `#temptable` doesn't exist when DSS considers the `SELECT * FROM #temptable` to infer the output schema.

You need to rewrite the query using `CREATE TABLE` and `INSERT INTO`.

Regards,

    Frederic

ps: you don't need the `-- DKU_END_STATEMENT`, and can use semi-colons to mark the end of a statement

 

View solution in original post

0 Kudos
antonstam
Level 2
Author
Re: Using temporary tables in SQL query recipes
Jump to solution

It's unfortunate that the SELECT INTO syntax is not supported, as it's an easy and flexible way to define a table. I can confirm that it works with a CREATE TABLE method:

CREATE TABLE #temptable (id INT, value INT);

INSERT INTO #temptable VALUES (1,1), (2,2);

SELECT * FROM #temptable
0 Kudos