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"

+3


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;

      

+2


source


Why don't you try with the union all

following:



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

      

+1


source


This will create a table

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

      

This will create the table as well.

SELECT * 
INTO #TestTable
FROM dual

      

Assuming double is a valid table in your database.

0


source







All Articles