Inserting Rows into an Oracle Table with Auto-Increment Sequence

Question

I have to insert rows into an Oracle table with an auto-increment sequence. The column definition for the 'ID' column is as follows:

"ID" NUMBER(10,0) DEFAULT "SC1"."DAT1_CTR_SEQ"."NEXTVAL" NOT NULL ENABLE?

Answer 1

Hi @Clover J,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

To insert rows into an Oracle table with an auto-increment sequence, you can try the following SQL statement:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Also, you can use the following command to return the inserted ID.

DECLARE
  v_id NUMBER;
BEGIN
  INSERT INTO your_table_name (ID, other_column1, other_column2)
  VALUES ("SC1"."DAT1_CTR_SEQ"."NEXTVAL", 'value1', 'value2')
  RETURNING ID INTO v_id;

  DBMS_OUTPUT.PUT_LINE('Inserted ID: ' || v_id);
END;

The RETURNING clause in the INSERT statement returns the value of the ID column that was inserted into the table, and the INTO clause assigns that value to the v_id variable. The DBMS_OUTPUT.PUT_LINE statement prints the inserted ID to the console.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.