SAP DB is now SAP MaxDB! For more information, visit the SAP MaxDB pages in the SAP Developer Network.

Sequence Value Used Several Times (Example)

Use

You have a number of options if you want to use a number generated by a sequence not just in a column of a table but also, for example, as a foreign key (FOREIGN KEY) in a different table:

• You need the determined value in the application for logging or for on-screen display and have to select the generated value
• You do not need the determined value in the application

Value is needed in the application

Syntax

CREATE SEQUENCE myseq

SELECT myseq.NEXTVAL INTO :hostvariable FROM DUAL

INSERT mytab1 VALUES (:hostvariable, ... remaining column values)

INSERT mytab2 VALUES (.. some column values..., :hostvariable, ... remaining column values)

If it is not possible to formulate a SELECT...INTO :hostvariable in the programming environment you use, this programming environment provides a way of formulating a SELECT without INTO :hostvariable and obtaining the next (or, in the case of table DUAL, the only) result. You then use this result for the following commands.

Value is not needed in the application

Syntax

CREATE SEQUENCE myseq

INSERT mytab1 VALUES (myseq.NEXTVAL, ... remaining column values)

INSERT mytab2 VALUES (... some column values..., myseq.CURRVAL, ... remaining column values)

This site is sponsored by SAP AG.