Oracle SQL Developer combine using "WITH AS", "INSERT INTO", SELECT"

Question

I am trying to figure out a way to do this.

I have a table with only read access, so let's call it existing_table. I want to have a reference table using "with as" statement and insert a new row to my reference table.

My code is: (pretend existing_table is ready for use)

INSERT INTO NEW_TABLE ( COLUMN_A, COLUMN_B)

VALUES (1, 'A')

WITH NEW_TABLE 

AS (SELECT * from EXISTING_TABLE)

SELECT * from NEW_TABLE

However, it doesn't work. Help please!!!!! "WITH" is where it gives me the error. if I move insert into statement after with as then "INSERT" is where it gives me the error.

My question is how can I use with/insert/select statement?

my guess is that you're missing a ; after the insert statement, and after your select statement, and whatever GUI/CLI you're using to run the queries in is trying to run them both, thinking they're one big statement. — Boneist
– Boneist, Commented Nov 28, 2019 at 10:43

Himanshu · Accepted Answer · 2019-11-27 20:40:28Z

1

Change the name of the identifier to some other name in WITH Clause as you have same name leading to ambiguity. Either way I guess you want like

   Create Table 
   NEW_TABLE AS SELECT *
   FROM EXISTING_TABLE;

   SELECT * FROM NEW_TABLE

answered Nov 27, 2019 at 20:40

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Wu Over a year ago

Thank you so much for replying. Could you send me an most simplified example with "insert into, with, and select" combined code without using any semicolon.

Boneist Over a year ago

@VincentWu why are you trying to avoid the semi-colon? You have two statements: an insert and a select. They cannot be combined into a single statement, because you can't both select and insert at the same time. You can insert data using a select statement, but you can't insert data and then expect to see what you've just inserted within a single statement.

Vincent Wu Over a year ago

@Boneist, the reason I want to avoid the second semi-colon is, the code will run under Power BI dashboard, Power BI query will give me error if I have two semi-colon.

Collectives™ on Stack Overflow

Oracle SQL Developer combine using "WITH AS", "INSERT INTO", SELECT"

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related