0

I am trying to transform some large data sets, and I have found the most optimized way is to write CTE's in Oracle. Performance wise, I found it to be very close in the findings from this article, (https://www.mssqltips.com/sqlservertip/5118/sql-server-cte-vs-temp-table-vs-table-variable-performance-test/).

However, the problem is that there are also tables which need to be created through this process in a sequential manner. However, I found out that although I am able to create tables using CTEs; I'm not able to use CREATE TABLE statements after CTEs, only before.

I have found that the following method would work. But some reason, I have to run the DROP TABLE & CREATE TABLE statements one at a time. Is there any way to modify the following code so that I can get both tmp and tmp2 tables updated just by running the script only once?

Code Example:

DROP TABLE tmp PURGE; 
DROP TABLE tmp2 PURGE; 

CREATE TABLE tmp ( tmp_id NUMBER(10));  
CREATE TABLE tmp2 ( tmp_id NUMBER(10));

/* Part I - Insert the first table using WITH CTE */
INSERT INTO tmp( tmp_id )
WITH cte AS (
  SELECT 1 AS tmp_id FROM dual union all
  SELECT 2 AS tmp_id FROM dual
)
SELECT tmp_id
FROM cte;

/* Part II- Use tmp table created above in another CTE*/
INSERT INTO tmp2( tmp_id )
WITH cte AS (
  SELECT tmp_id from tmp
)
SELECT tmp_id
FROM cte;

--/*Just for testing*/ select * from tmp
--/*Just for testing*/ select * from tmp2
Yen Ching
  • 11
  • 1
  • 4
  • Why do you bother to drop and recreate the tables? If you want to erase the data just use truncate on each table - yes 1 at a time. BTW your comments about creating the table with CTE are incorrect; the tables have already been created, your CTEs are just populating them. – Belayer Dec 13 '19 at 16:30
  • Hi Belayer, thank you for the advisement on the comment. I can change to TRUNCATE instead of CREATE. However, is there any way to modify the tables all at once? That is the crux of the problem. Thanks-Yen – Yen Ching Dec 13 '19 at 16:48
  • 1
    You can try to do a [multi-table insert](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423)? – gsalem Dec 13 '19 at 16:54
  • 1
    Yes. Truncate removes all data from the table without creating rollback possibilities. It actually resets the high water mark for the table thus effectively erasing all the data. But the table structure(s), including constraints, triggers, etc remain valid. Drop and recreate removes the data but also the structure(s). – Belayer Dec 13 '19 at 16:56
  • I don't understand this at all: *I found out that although I am able to create tables using CTEs; I'm not able to use CREATE TABLE statements after CTEs, only before.* What isn't working exactly? Then you end by asking about updates, but your example doesn't contain any. – William Robertson Dec 14 '19 at 09:33

1 Answers1

0

The reason why you are not able to use CREATE TABLE statements after CTEs, is because CTE are part of a SELECT instruction. It wouldn't make sense to 'SELECT * from table DELETE TABLE2'. It has to be 2 separate instruction.

Why do you have to DROP then CREATE then INSERT in your table? You can insert your data directly into a new table, that would be only one instruction. see : SQL Server SELECT into existing table

If you want to rerun your script, you could just truncate your table before executing your insert, as Belayer said in comment

Richard Matriche
  • 87
  • 7