CODEWITHSUNDEEP
sqloracle-databaseplsqlinsertsequence
null
null

Reputation: 131

Insert a sequence and SELECT

I'm trying to insert into a table, where the ID is the next in a sequence, and some of the other values are pulled from a table. I know that this won't work:

INSERT INTO ORDERS(order_id, foo1, foo2, foo3
VALUES(SEQUENCE_ORDERS.nextval,(SELECT foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y))

I just tried, and it I get a "not enough values" error.

I wanted to know what the correct syntax was to get this to work. I'm using PLSQL in an Oracle DB (part of a procedure I'm writing).

I know that the select should not be inside the VALUES() but how do I also add the sequence? The only option I can think of would be a trigger on the insert, but I'd rather not have to do that.

EDIT: thank you all, just as I figured it out, everyone posted. Didn't know it was ok to have the sequence in the select.

Upvotes: 0

Views: 14485

Answers (4)

Vijay
Vijay

Reputation: 1

INSERT into DON AUDITI (DON SEO_ID. NEXTVAL, I OLDCUSTED, I MENCUSTED) values ('T_CRED NEXUS _CUST ACCESS' , I. PEOPLEVISE_ID, I_CREATEDBY, SYSDATE);

Upvotes: 0

Cos Callis
Cos Callis

Reputation: 5084

Try:

INSERT INTO ORDERS(order_id, foo1, foo2, foo3
SELECT SEQUENCE_ORDERS.nextval, foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y

Upvotes: 1

dcp
dcp

Reputation: 55467

INSERT INTO ORDERS(order_id, foo1, foo2, foo3)
SELECT SEQUENCE_ORDERS.nextval,foo1, foo2, foo3
                                FROM bar
                                WHERE X = Y

Upvotes: 9

Lost in Alabama
Lost in Alabama

Reputation: 1653

Try moving the SEQUENCE over to the SELECT statement.

Upvotes: 0

Related Questions