Question
Augie Turano · Oct 9, 2020

How to Create a Cache SQL Temp Table?

In MS SQL Server i can do this:

SELECT 
Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'

The code would create  a new temporary table with the fields defined from Aviation.Crew.   I cannot find how to do this in Cache SQL in the documentation.  Can someone show me the correct syntax or other solution?  This is a very simple example but i have very large tables with a lot of fields  that require numerous filters and where clauses,  having this type of mechanism would be extremely useful.

Thanks -- 

00
0 8 169 2

Replies

in IRIS you have to do it in 2 steps

  • CREATE GLOBAL TEMPORARY TABLE tempfemale (     Category,      CrewNumber,      MedicalCertificationDate,      Seat,      SeatbeltUsed,      Sex,      ShoulderHarnessUsed,      ToxicologyTestPerformed,      childsub ) see: https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.cls?KEY=RSQL_createtable#RSQL_createtable_temp  
  • and INSERT  INTO tempfemale       SELECT       Category, CrewNumber, MedicalCertificationDate,Seat,                    SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed,                                childsub               FROM Aviation.Crew               WHERE Sex = 'F'

see:  https://docs.intersystems.com/irisforhealthlatest/csp/docbook/Doc.View.cls?KEY=RSQL_insert#RSQL_insert_select 

Unless there is something i am missing there is no real advantage to having a temp table unless you are worried about storage.  From the code above I would have to manually create an identical table then run my filters to populate it.  That is not as simple as the Microsoft code that i originally posted.   Also in MSFT Studio i can script out the schema so that i would not have to type in the entire schema.  Is there a way to do that in Cache SQL.   From the GUI i can see the table structure but i don't see a way to create a script to regenerate the table?

"there is no real advantage to having a temp table unless you are worried about storage."

"That is not as simple as the Microsoft code"

Both observations are correct.
Though there is an implementation of TSQL that mentions SELECT ... INTO explicitly
But you have to declare that your context is TSQL

but this not Cache SQL anymore as initially inquired

Hi Angie

How about using a VIEW instead?

Jenna

[Deleted - I linked the documentation that Robert had shared having not seen he'd already answered]

Robert is correct, you can leverage our TSQL support to mimic that INSERT SELECT model Microsoft and Sybase offer. I would not recommend mixing TSQL with plain SQL on an ongoing basis to build your non-TSQL application, but just using the command on a one-off basis (it's a temp table you're after anyhow) should be just fine.

in IRIS SQL (and most other databases), INSERT SELECT will just do that, insert based on the selection, and not implicitly create the table if it doesn't exist. For that purpose, the CREATE TABLE .. AS SELECT .. syntax is offered by several other databases, and will appear in IRIS SQL soon. As a matter of fact, a developer is working on that command this very week :-)