How to Create a Cache SQL Temp Table?

Question

Question

Augie Turano · Oct 9, 2020

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

Robert Cemper · Oct 9, 2020

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

2 0

Robert Cemper · Oct 10, 2020

"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

2 0

Vitaliy Serdtsev · Aug 19, 2022

See documentation for $SYSTEM.SQL.QueryToTable(): THIS METHOD IS DEPRECATED. Refer to $SYSTEM.SQL.Schema.QueryToTable instead.^2021.1

Am I correct in saying that a create table as select is strictly as COPY operation of an existing table.

Yes, see CREATE TABLE table AS SELECT query copies column definitions and column data from an existing table (or tables) into a new table based on the specified SELECT query.^2022.1

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?
Benjamin De Boe

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

Because this feature has appeared since version IRIS 2022.1 (see link above).

1 0

score 0 · Answer 1 · 2020-10-10T15:45:20-04:00

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?

score 0 · Answer 2 · 2020-10-10T17:28:18-04:00

If you are on VSCode try SQL Tools InterSystems addon - it has schema code completion. Is this what you are looking for?

score 0 · Answer 3 · 2020-10-09T20:32:26-04:00

Jenna Poindexter · Oct 9, 2020

Hi Angie

How about using a VIEW instead?

Jenna

0 0

score 0 · Answer 4 · 2020-10-10T03:10:12-04:00

Julian Matthews · Oct 10, 2020

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

0 0

score 0 · Answer 5 · 2020-10-12T03:37:02-04:00

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

score 1 · Answer 6 · 2020-10-14T06:57:34-04:00

Vitaliy Serdtsev · Oct 14, 2020

See also $SYSTEM.SQL.Schema.QueryToTable()

1 0

score 0 · Answer 7 · 2022-08-16T14:38:40-04:00

@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?

score 0 · Answer 8 · 2022-08-22T03:07:06-04:00

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.