How to Use SEQUENCE with INSERT Statement for Auto-Incrementing IDs in Oracle

In Oracle databases, a SEQUENCE is a built-in object designed to generate unique numeric values in a defined order. In this article, we’ll learn how to use a SEQUENCE directly within an INSERT statement to achieve auto-incrementing IDs, similar to MySQL’s AUTO_INCREMENT or SQL Server’s IDENTITY, without relying on triggers. For developers seeking a reliable and flexible way to implement Oracle INSERT auto increment functionality, this approach offers clear control and simplicity.

Table of Contents:

• What is a SEQUENCE in Oracle?
• How to use SEQUENCE with INSERT in Oracle?
• Examples of using SEQUENCE with the INSERT Statement on Oracle
• Why Use SEQUENCE with INSERT Instead of Triggers or Defaults?
• Difference Between SEQUENCE with INSERT vs Other Methods
• Conclusion

What is a SEQUENCE in Oracle?

A SEQUENCE is an autonomous database object that generates numeric values in a specific order. It is commonly used to create Oracle auto increment ID fields by combining it with the NEXTVAL keyword in INSERT statements. This approach is especially useful when you want to auto-generate ID in Oracle without trigger logic.

How to use SEQUENCE with INSERT in Oracle?

You must call sequence_name to use a sequence directly with an INSERT statement. To obtain the following number in the sequence, use NEXTVAL. This guarantees that each new row has a unique ID that is automatically increased. The fact that the sequences exclude the gaps in numbers across the multiple entries of users, which are occurring simultaneously, ensures that they are used frequently with TRIGGERs, sometimes as part of the INSERT statements for effective management of IDs.

Syntax:

--We create a separate sequence and use it manually in the INSERT Statements
CREATE SEQUENCE sequence_name
START WITH initial_value  
INCREMENT BY step_value  
[optional parameters];  
CREATE TABLE table_name (
    column_name NUMBER PRIMARY KEY,
    other_column_name column_type(size),
    ...
);
--Inserting data using SEQUENCE
INSERT INTO table_name (column1, column2, column3, ...) 
VALUES (sequence_name.NEXTVAL, value2, value3, ...);
--To display the data 
SELECT * FROM table_name;

Examples of using SEQUENCE with the INSERT Statement on Oracle

Now, let’s see some examples for using SEQUENCE with the INSERT Statement on Oracle for creating an ID with AUTO_INCREMENT. Below is the Oracle INSERT with sequence NEXTVAL example.

Example 1:

--To create a separate sequence
CREATE SEQUENCE employee_seq
START WITH 1 
INCREMENT BY 1 
NOCYCLE NOCACHE;
CREATE TABLE Employees (
    Employee_id NUMBER PRIMARY KEY,
    Employee_dept NVARCHAR2(50),
    Salary DECIMAL(10,2)
);
--Insert into the table
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'HR', 50000.00);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'Finance', 60000.50);
INSERT INTO Employees (Employee_id, Employee_dept, Salary)  
VALUES (employee_seq.NEXTVAL, 'IT', 75000.75);
--To display the table
SELECT * FROM Employees;

Output:

Explanation: Employee_seq generates unique numeric IDs. The Employee_id is not auto-generated, so we can manually assign values using the sequence.

Example 2:

-- Create a sequence for the orders table
CREATE SEQUENCE orders_seq
START WITH 1000
INCREMENT BY 1
NOCYCLE NOCACHE;
-- Create the Orders table
CREATE TABLE Orders (
    Order_id NUMBER PRIMARY KEY,
    Customer_name NVARCHAR2(100),
    Order_amount DECIMAL(10,2)
);
-- Insert records using the sequence
INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'John Doe', 250.75);
INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'Jane Smith', 400.50);
INSERT INTO Orders (Order_id, Customer_name, Order_amount)  
VALUES (orders_seq.NEXTVAL, 'Michael Brown', 150.25);
-- Display the table data
SELECT Order_id,Order_amount FROM Orders;

Output:

Explanation: Here, the orders_seq starts from 1000 and increases by 1 for every inserted row. The INSERT statement uses orders_seq.NEXTVAL to auto-generate unique order IDs.

Why Use SEQUENCE with INSERT Instead of Triggers or Defaults?

Using a SEQUENCE with an INSERT statement offers precise control over how and when IDs are generated in Oracle databases. While Oracle provides options like triggers or DEFAULT sequence_name.NEXTVAL, manually referencing the sequence in the INSERT statement, gives developers more flexibility. Here’s why this method is often preferred:

1. Full Control Over ID Generation
When you explicitly call sequence_name.NEXTVAL in the INSERT statement, you determine exactly when a new ID is generated. This is particularly helpful when you need to:

• Assign IDs conditionally based on business logic
• Reuse or skip sequence numbers in specific scenarios
• Ensure consistency across multiple tables using the same sequence

This level of control is not available when relying on DEFAULT values or implicit triggers.

2. Avoids Hidden or Implicit Logic
Triggers can obscure application behavior since they operate behind the scenes. If a developer or DBA is unaware of an existing trigger, it can lead to:

• Unexpected results during debugging or data auditing
• Complicated migration or replication processes
• Performance overhead due to implicit trigger execution

Using explicit SEQUENCE in INSERT avoids this by keeping the ID generation visible in the SQL code, making it easier to maintain and audit.

3. Ideal for Scripts, Migrations, and Legacy Integrations
When performing bulk inserts, data migrations, or working with legacy systems, you often need to preserve or control ID sequences carefully. Manually inserting values using a SEQUENCE:

• Allows backfilling specific IDs without altering logic
• Helps during data imports when triggers could conflict with legacy data
• Ensures batch operations do not fail due to sequence-related trigger logic

4. Best Way to Auto-Generate ID in Oracle Without a Trigger
If your goal is to auto-generate IDs in Oracle without using triggers, this method is the most straightforward. It’s transparent, easy to implement, and doesn’t depend on Oracle 12c features like IDENTITY or DEFAULT sequence.NEXTVAL.
Whether you’re dealing with concurrent inserts or building portable scripts across Oracle versions, using SEQUENCE with INSERT gives you reliable, version-independent control over Oracle auto increment ID behavior.

Difference Between SEQUENCE with INSERT vs Other Methods

Conclusion

A straightforward and efficient method of achieving AUTO_INCREMENT capabilities in Oracle is to use a SEQUENCE with an INSERT statement directly. Without the use of triggers, it guarantees sequential and unique ID generation. Developers have complete control over ID management by setting sequence attributes like START WITH, INCREMENT BY, and CACHE. This method performs well where several INSERT statements take place at once. It is still a popular and effective way to deal with auto-incrementing primary keys in Oracle databases, even though it needs to be used explicitly in INSERT statements.

Other Methods for Creating an ID with AUTO_INCREMENT on Oracle

• Using a SEQUENCE and TRIGGER on Oracle SQL
• Using a SEQUENCE With DEFAULT on Oracle SQL
• Using a BEFORE INSERT Trigger with MAX(ID) on Oracle SQL
• Using a GUID on Oracle SQL
• Using an IDENTITY Column on Oracle SQL
• Using a Combination of SEQUENCE and GUID on Oracle SQL
• Using a BEFORE INSERT TRIGGER with Custom Logic on Oracle SQL

How to Use SEQUENCE with INSERT Statement for Auto-Incrementing IDs in Oracle – FAQs