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"
code:
WITH ABC(one,two,three)
AS(
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
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;
source to share