Question
· 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 -- 

Discussion (11)1
Log in or sign up to continue

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?

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 :-)

@Benjamin De Boe 
 

I went nuts today working with some of this stuff.  Can I confirm a few points:

- $SYSTEM.SQL.Schema.QueryToTable() does not exist in my IRIS version however $SYSTEM.SQL.QueryToTable() does.  I have IRIS 2021. Are these different things?

- I ended up using a CREATE OR REPLACE VIEW because doing a CREATE TABLE ... AS SELECT would create a table but return no data (I would even do a DROP TABLE operation first to make sure all was clear.  Am I correct in saying that a create table as select is strictly as COPY operation of an existing table.  Since I was effectively creating a new table with new headers, the view worked better here.  Is that the difference?

- To clarify, I used the $SYSTEM command to create table from query, because I kept getting errors with create table as select (didn't like my AS statement).  What could have gone wrong there?

Thanks @Vitaliy Serdtsev 

Indeed, that method will be deprecated shortly, in favour of CREATE TABLE AS SELECT, which is new in 2021.1 and part of the SQL standard. Both this command and the $SYSTEM utility method will create a physical copy of the data, so a real SQL table that's not kept in sync in the way a SQL view is. If the latter is what you need, don't bother with creating it as a table.

Note that in a more recent version we also support the CREATE OR REPLACE syntax for a bunch of additional statements (including CREATE FUNCTION, CREATE PROCEDURE, ...) and CREATE IF NOT EXISTS for things that actually contain data such as tables.