SELECT QUERY within ORACLE INSERT QUERY

Question

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

Pரதீப் · Accepted Answer · 2016-08-24 10:07:30Z

6

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'

edited Aug 24, 2016 at 10:07

answered Aug 24, 2016 at 10:01

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashwin Sharma Over a year ago

Hey,Thanks for the quick response Each time I don't do the same way, 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

Collectives™ on Stack Overflow

SELECT QUERY within ORACLE INSERT QUERY

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related