SELECT QUERY within ORACLE INSERT QUERY

3
votes

I am using ORACLE 10g as database.

Consider two tables:
1.) ABC with column A as primary key and rest can hold null values
2.) XYZ with column W as primary key and rest can hold null values

Also the datatypes for peers are same
Example: A = W , B=X , C=Y , D=Z
Equality means the datatypes are same

The following query runs perfectly fine

INSERT INTO ABC(A ,B,C,D) 
VALUES ('klm'  , (SELECT X FROM XYX WHERE W ='SOME_VALUE') , 'Dsl' , 'rwz')

But the following query doesn't work.. If someone could help me out ? 

INSERT INTO ABC(A,B,C,D) 
VALUES ( (SELECT W, X , Y , Z FROM XYX WHERE W ='SOME_VALUE') )

NOTE: The value of W is not in table ABC. No Constraint violation

Error report: SQL Error: ORA-00947: not enough values 00947. 00000 - "not enough values"

Please explain??

Insert into ABC(A,B,C,D) VALUES('123' , (SELECT X,Y,Z from XYZ where W = 'same_value')) I need a solution to this generic thing

1
sqloracle

1 Answers

6
votes

Remove the values constructor and use the Select query directly after the Insert.

Try this way

INSERT INTO ABC(A,B,C,D) 
SELECT W, X , Y , Z FROM XYX WHERE W ='SOME_VALUE'

Update

Just hard code the value(123) in Select list

INSERT INTO ABC(A,B,C,D) 
SELECT 123, X , Y , Z FROM XYX WHERE W ='SOME_VALUE'