ORACLE - Insert from a Select

Question

ORACLE - Insert from a Select

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (-2 votes)

1

someone helps me solve a problem I have (beats me that is layer 8 :)), well look, if I do this sql works normal:

insert into USR_SISTEMAS VALUES ('16469', '2','3')

but if the data I want to bring a select as:

insert into USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) VALUES (select 16469, '2', '3' from dual)

or like this:

insert into USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) VALUES (select 16469 as SIST_ID,'2' AS DESCRIPCION,'3' AS ALIAS from dual)

because it simply gives me the following error:

Error que empieza en la línea: 54 del comando -
insert into USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) VALUES (select 16469 as SIST_ID,'2' AS DESCRIPCION,'3' AS ALIAS from dual)
Error en la línea de comandos : 54 Columna : 64
Informe de error -
Error SQL: ORA-00936: falta una expresión
00936. 00000 -  "missing expression"
*Cause:    
*Action:

Someone by fa helps me to decipher why is this error, I thank you in advance.

Best regards to all

sql oracle

asked by RSillerico 17.03.2017 в 00:45

source

3 answers

Open a fragment from another fragment of different activity Uncaught ReferenceError: PrimeFaces is not defined

score 1 · Answer 1

I have never done it but, checking a bit on the Internet, I found the following structure:

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

In your case it would be as follows:

INSERT INTO USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) SELECT 16469 as SIST_ID,'2' AS DESCRIPCION,'3' AS ALIAS FROM dual;

For May information you can consult:

link

Or this SO question in English:

link

score 0 · Answer 2

0

You must take the Select of the parentheses, as it is not a value as such.

INSERT INTO USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) 

SELECT 
    16469 as SIST_ID,
    '2' AS DESCRIPCION,
    '3' AS ALIAS 
FROM DUAL

answered by 13.06.2017 в 21:44

score -2 · Answer 3

Indicates the fields of the table in which you want to insert the values, otherwise you will be taken as if you were to insert the 3 fields in a column and that leads to an error.

One way that sure works for you is:

insert into USR_SISTEMAS (SIST_ID, DESCRIPCION, ALIAS) VALUES ((select 16469 from dual), (Select '2' from dual), (select '3' from dual))

Put the select in parentheses so that SQL knows that the select is part of one field and not several.