SQL insert statement - select keyword is missing

I am very new to SQL language and am trying to execute the following code. This gives me the error below. What I did wrong?

Error message:

ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"



WITH ABC(one,two,three)
  select 25, 15,23 from dual
INSERT INTO ABC(one, two, three) VALUES (10,11,12)
select * from ABC;


Thank you in advance!

EDIT - Explanation
I need to create a table with more than 100 rows. I cannot create a new table due to sufficient privilege, so I am trying to create a virtual table. The idea on my head is to insert rows into a virtual table using a For-loop. But this "keyword missing" error occurs when I try to insert any record ...

EDIT - Regarding the word "very new to the SQL language"
I am currently a computer engineering student at university and took the Database course last year. Now I am doing an internship. So .. I'm not like a beginner looking for a w3school tutorial, but I'm new to "real life SQL"


source to share

3 answers

You need another "table" that returns the number of rows required. In Oracle, this is usually done using the undocumented operator function connect by


select level as rn 
from dual
connect by level <= 100


The "undocumented" part is the fact that no real "connection" is being made, but <<22>. Read more about this connect by

in the manual.

will return 100 lines. You can combine this with your original CTE and cross-join to return one line a hundred times:

WITH abc (one,two,three) AS (
  select 25, 15,23 from dual
), num_rows as (
  select level as rn 
  from dual
  connect by level <= 100
select abc.* 
from abc
  cross join num_rows;




Why don't you try with the union all


WITH ABC(one,two,three)
  select 25, 15,23 from dual union all
  select 10,11,12 from dual
select * from ABC




This will create a table

SELECT (21)as a,(22)as b,(23)as c
INTO #Test


This will create the table as well.

INTO #TestTable
FROM dual


Assuming double is a valid table in your database.



All Articles