How to Insert Data in Oracle SQL

In this article, we’ll explore how to insert data in Oracle SQL but first, let’s discuss why this operation is essential.

Inserting data lays the foundation for all subsequent data operations, from querying to reporting and analytics

Why Is Data Insertion in SQL Important?

Inserting data lays the foundation for all subsequent data operations, from querying to reporting and analytics. For instance, in the healthcare industry, inserting data would ensure that every patient’s medical records are up to date, enabling precise diagnoses and effective care. Proper data insertion supports informed decision-making.

Understanding the INSERT statement

The INSERT statement adds data into your SQL table in all the various forms of SQL, not just Oracle SQL.

Now let’s explore basic INSERT syntax!

Basic Syntax

Below is the basic syntax for the INSERT statement in Oracle SQL:

INSERT INTO table

(column1, column2, ... column_n )

VALUES

(value1, value2, value3, ...);

Key Elements of a Table Definition

The INSERT statement syntax comes with the following arguments:

• table: the table you want to insert data into
• column1, column2, … : the columns in the tables
• expression1, expression2, … : the data that should be inputted as a record

Types of INSERT statements

You can insert data into your database using either of these types of INSERT statements:

• Single-row insert
• Multi-row inserts

How to Insert Data in Oracle SQL

To insert data in Oracle SQL, you need the following prerequisites:

• Oracle database management system. Use Oracle Database 19c or 21c.
• Oracle SQL Developer, a graphical user interface (GUI) tool for interacting with Oracle databases.

Once you have these installed, you can insert data into your Oracle database using the INSERT statement and the following steps:

1. Launch the Oracle database management system.
2. Connect to your database.
3. Open Oracle SQL Developer.
4. Write the INSERT statement specifying:
   1. Table name
   2. Column names (comma separated)
   3. VALUES clause with corresponding data
5. Run the query.

Let’s explore how you can insert data using either of the two INSERT types.

Using Single-Row Insert

A single row insert allows you to add individual records to your table.

Below is the syntax for a single-row insert:

INSERT INTO table

(column1, column2, ... column_n )

VALUES

(value1, value2, value3, ...);

Let’s assume you have a “customers” table:

INSERT INTO Customers

(customer_id, name, email, phone_number, registration_date)

VALUES

(1001, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', '01-JAN-2025');

When inserting data, there are some rules you have to follow:

• Numeric data should not be delimited.
• String character and date data should use single quotes.
• Null values should be inputted as NULL.
• Values should be separated by commas.
• Use TO_DATE or the literal date (as above) when inserting dates. Alternatively, you can run the script below to set the session’s default date format you want.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

Common Use Cases

A single-row insert is primarily used to add new records to a database.

Handling Constraints and Errors

Here are some potential constraints and errors you might come across:

• Data type mismatches: This can happen when you input data into a column that requires another data type, such as inputting a string character into a date column.
• Not NULL constraints: This happens if you insert a NULL value into a column with a Not NULL constraint.

Working With Multi-Row Inserts

A multi-row insert allows you to add multiple records to your table. Below is the syntax for a multi-row insert:

INSERT ALL

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

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

...

SELECT * FROM dual;

The SELECT * FROM dual is a dummy SELECT from the dual table (a one-row, one-column table in Oracle SQL) used to complete the operation. This allows you to select a value without needing to reference an actual table, thus serving as a placeholder for queries.

Now let’s insert multiple rows of values into various rows in the “customers” table:

INSERT ALL

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (1002, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', TO_DATE('01-01-2025', 'DD-MM-YYYY'))

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (1003, 'Tricentis', 'office@tricentis.com', '737-497-9993', TO_DATE('08-01-2025', 'DD-MM-YYYY'))

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (1004, 'Random', 'Random@gmail.com', '410-979-5637', TO_DATE('08-01-2025', 'DD-MM-YYYY'))

SELECT * FROM dual;

Alternately, you can use the following syntax:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value4, value5, value6, ...);

This will translate into the following:

INSERT INTO Customers VALUES (11, 'Benny', 'iheifeanyi@gmail.com',

'530-203-2867', TO_DATE('01-01-2025', 'DD-MM-YYYY'));

INSERT INTO Customers VALUES (12, 'Tricentis', 'office@tricentis.com',

'737-497-9993', TO_DATE('08-01-2025', 'DD-MM-YYYY'));

Performance Considerations

Below are some performance considerations you should consider when working with multi-row inserts in Oracle SQL:

• Use multi-row INSERT statements for better performance. The Simple Talk SQL test showed that inserting 25 rows took 9 seconds with multi-row inserts versus 57 seconds with single-row inserts.
• Consider deferring index updates until after the insertion is complete to improve performance. This allows the database to update indexes in bulk rather than row by row.
• Multi-row inserts reduce context switching, avoiding repeated database connections, query parsing, and index updates, which are resource intensive.
• Wrap your inserts in a transaction and commit after all rows are inserted for greater efficiency.
• Use the Procedural Language/Structured Query Language (PL/SQL) bulk processing with FORALL to minimize context switches.

-- Step 1: Define a collection type

DECLARE

TYPE customer_rec IS RECORD (

customer_id INT,

name VARCHAR2(100),

email VARCHAR2(100),

phone_number VARCHAR2(15),

registration_date DATE

);

TYPE customer_tbl IS TABLE OF customer_rec; -- Define a table of records

l_customers customer_tbl; -- Declare a variable of the collection type

BEGIN

-- Step 2: Populate the collection with data

l_customers := customer_tbl(

customer_rec(10, 'John Doe', 'john.doe@example.com', '123-456-7890', SYSDATE),

customer_rec(20, 'Jane Smith', 'jane.smith@example.com', '098-765-4321', SYSDATE),

customer_rec(30, 'Alice Johnson', 'alice.johnson@example.com', '555-123-4567', SYSDATE)

);

-- Step 3: Use FORALL for bulk insert

FORALL i IN l_customers.FIRST .. l_customers.LAST

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (l_customers(i).customer_id, l_customers(i).name, l_customers(i).email,

l_customers(i).phone_number, l_customers(i).registration_date);

COMMIT; -- Commit after all inserts are done

END;

Handling Constraints and Duplicate Rows

When performing multi-row inserts, make sure there are no violations of primary keys or unique constraints.

• Check existing values before executing your INSERT query.
• Use conditional INSERT logic to insert only non-duplicate records.

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

SELECT 1001, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', TO_DATE('08-01-2025', 'MM-DD-YYYY')

FROM dual

WHERE NOT EXISTS (

SELECT 1 FROM Customers WHERE customer_id = 1001

);

Since customer_id 1001 already exists in the “customer” table, no new record was inserted.

Use the MERGE statement to manage duplicates effectively.

MERGE INTO Customers c

USING (SELECT 1001 AS customer_id, 'Benny' AS name, 'iheifeanyi@gmail.com' AS email,

'530-203-2867' AS phone_number, TO_DATE('01-01-2025', 'MM-DD-YYYY') AS registration_date

FROM dual) src

ON (c.customer_id = src.customer_id)

WHEN MATCHED THEN

UPDATE SET c.name = src.name,

c.email = src.email,

c.phone_number = src.phone_number,

c.registration_date = src.registration_date

WHEN NOT MATCHED THEN

INSERT (customer_id, name, email, phone_number, registration_date)

VALUES (src.customer_id, src.name, src.email, src.phone_number, src.registration_date);

In this query, the MERGE statement checks if a record with customer_id = 1001 exists in the “customers” table. If it does, it updates the details. Otherwise, it inserts the new record.

Advanced INSERT Techniques

Here are some advanced INSERT techniques used in SQL Oracle:

1. Using INSERT With SELECT

This method allows you to insert data from one table into another. It’s important when copying data from another table.

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

WITH customer_data AS (

SELECT 10010 AS customer_id, 'Benny' AS name, 'iheifeanyi@gmail.com' AS email, '530-203-2867' AS phone_number, TO_DATE('08-01-2025', 'MM-DD-YYYY')

AS registration_date FROM dual UNION ALL

SELECT 10020, 'Tricentis', 'office@tricentis.com', '737-497-9993', TO_DATE('08-01-2025', 'MM-DD-YYYY')

FROM dual UNION ALL

SELECT 10030, 'Random', 'Random@gmail.com', '410-979-5637', TO_DATE('08-01-2025', 'MM-DD-YYYY')

FROM dual

)

SELECT * FROM customer_data;

2. INSERT All for Conditional Inserts

This method allows you to perform conditional inserts into one or more tables based on specific criteria.

INSERT ALL

WHEN customer_id = 10001 THEN

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (10001, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', TO_DATE('08-01-2025', 'MM-DD-YYYY'))

WHEN customer_id = 10002 THEN

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (10002, 'Tricentis', 'office@tricentis.com', '737-497-9993', TO_DATE('08-01-2025', 'MM-DD-YYYY'))

WHEN customer_id = 10003 THEN

INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (10003, 'Random', 'Random@gmail.com', '410-979-5637', TO_DATE('08-01-2025', 'MM-DD-YYYY'))

SELECT 10001 AS customer_id FROM DUAL

UNION ALL

SELECT 10002 AS customer_id FROM DUAL

UNION ALL

SELECT 10003 AS customer_id FROM DUAL;

3. INSERT With RETURNING Clause

The RETURNING clause allows you to get values from rows that have just been inserted. This is useful when obtaining generated values like primary keys.

DECLARE

v_customer_id NUMBER;

BEGIN

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (1007, 'New Customer', 'newcustomer@example.com', '555-1234', SYSDATE)

RETURNING customer_id INTO v_customer_id;

DBMS_OUTPUT.PUT_LINE('Inserted Customer ID: ' || v_customer_id);

END;

” class=”navLinkPadding”>Inserting Large Volumes of Data

Here are some ways you can insert large volumes of data into Oracle SQL databases.

• TheINSERT ALL statement allows you to insert multiple rows in a single SQL command, reducing the number of database calls needed.

INSERT ALL

INTO Customers (customer_id, name, email, phone_number, registration_date) VALUES (1011, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', '08-01-2025')

INTO Customers (customer_id, name, email, phone_number, registration_date) VALUES (1012, 'Tricentis', 'office@tricentis.com', '737-497-9993', '08-01-2025')

INTO Customers (customer_id, name, email, phone_number, registration_date) VALUES (1013, 'Random', 'Random@gmail.com', '410-979-5637', '08-01-2025')

SELECT * FROM dual;

Remember you will need to alter your session using ALTER SESSION before you can run the query above.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

• The FORALL statement minimizes context switching between SQL and PL/SQL engines.

DECLARE

TYPE CustomerRec IS RECORD (

customer_id NUMBER,

name VARCHAR2(100),

email VARCHAR2(100),

phone_number VARCHAR2(15),

registration_date DATE

);

TYPE CustomerTable IS TABLE OF CustomerRec INDEX BY PLS_INTEGER;

customers CustomerTable;

BEGIN

-- Populate the collection with customer data

customers(1) := CustomerRec(1021, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', TO_DATE('08-01-2025', 'MM-DD-YYYY'));

customers(2) := CustomerRec(1022, 'Tricentis', 'office@tricentis.com', '737-497-9993', TO_DATE('08-01-2025', 'MM-DD-YYYY'));

customers(3) := CustomerRec(1023, 'Random', 'Random@gmail.com', '410-979-5637', TO_DATE('08-01-2025', 'MM-DD-YYYY'));

-- Use FORALL to insert all records in the collection

FORALL i IN customers.FIRST .. customers.LAST

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (customers(i).customer_id, customers(i).name, customers(i).email, customers(i).phone_number, customers(i).registration_date);

COMMIT; -- Commit the transaction after the bulk insert

END;

Upload your data as a CSV file by loading it into an Oracle database.

Strategies for Optimizing Insert Performance

Here are several strategies for optimizing INSERT performance in Oracle SQL:

• Using direct-path inserts by using APPEND or APPEND_VALUES hints in your INSERT statements for faster data insertion.

INSERT /*+ APPEND */ INTO your_table SELECT * FROM your_source_table;

• Using bulk inserts with the INSERT ALL or FORALL statement.
• Commit in batches to reduce the number of commits.
• Use smaller data types where possible to reduce the amount of data being written to disk.
• Consider disabling indexes and constraints before performing large insert operations to reduce the overhead during inserts.

Error Handling in Bulk Inserts

The SAVE EXCEPTIONS clause allows you to continue processing records even if they fail during a bulk insert.

DECLARE

-- Define a record type that matches the structure of the Customers table

TYPE customer_rec IS RECORD (

customer_id NUMBER,

name VARCHAR2(100),

email VARCHAR2(100),

phone_number VARCHAR2(15),

registration_date DATE

);

-- Define a collection type to hold multiple customer records

TYPE customer_tab IS TABLE OF customer_rec;

-- Initialize the collection with sample data

l_customers customer_tab := customer_tab(

customer_rec(1001, 'Benny', 'iheifeanyi@gmail.com', '530-203-2867', TO_DATE('08-01-2025', 'MM-DD-YYYY')),

customer_rec(1002, 'Alice', 'alice@example.com', '530-203-2868', TO_DATE('08-01-2025', 'MM-DD-YYYY')),

customer_rec(1003, 'Bob', 'bob@example.com', '530-203-2869', TO_DATE('08-01-2025', 'MM-DD-YYYY'))

);

l_error_count NUMBER; -- Variable to hold the count of errors

BEGIN

-- Use FORALL to perform bulk inserts with error handling

FORALL i IN 1 .. l_customers.COUNT SAVE EXCEPTIONS

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (l_customers(i).customer_id, l_customers(i).name, l_customers(i).email, l_customers(i).phone_number, l_customers(i).registration_date);

EXCEPTION

WHEN OTHERS THEN

l_error_count := SQL%BULK_EXCEPTIONS.COUNT; -- Get the number of errors

DBMS_OUTPUT.PUT_LINE('Number of failures: ' || l_error_count);

-- Loop through each error and output details

FOR i IN 1 .. l_error_count LOOP

DBMS_OUTPUT.PUT_LINE('Error on index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));

END LOOP;

END;

Oracle’s DML error logging allows you to log errors in an error table without manually handling exceptions in your PL/SQL code.

Managing Transactions in INSERT Operations

Transactions allow you to execute multiple SQL statements as a single unit of work while following the ACID properties (atomicity, consistency, isolation, durability). This helps you maintain data integrity and consistency in your Oracle databases.

To manage transactions during the INSERT operation, follow these steps:

1. Start your query with a BEGIN or START TRANSACTION.
2. Add your INSERT statements.
3. End the query with a COMMMIT to save changes if your operations succeed.

For your customer table:

BEGIN

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (3001, 'John Sakura', 'john.Sa@gmail.com', '123-456-7890', '01-02-2025');

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (4001, 'Benny Killua', 'Benny.Ki@gmail.com', '098-765-4321', '01-02-2025');

COMMIT;

END;

You can take this a step further by implementing a ROLLBACK to handle potential errors during the INSERT operations. Here’s an example:

BEGIN

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (3001, 'John Sakura', 'john.Sa@gmail.com', '123-456-7890', '01-02-2025');

INSERT INTO Customers (customer_id, name, email, phone_number, registration_date)

VALUES (4001, 'Benny Killua', 'Benny.Ki@gmail.com', '098-765-4321', '01-02-2025');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || SQLERRM);

END;

Best Practices for Using Oracle SQL INSERT

Here are some best practices you should follow when using the Oracle SQL INSERT statement:

• Always specify the column names in your INSERT statements.
• Validate the data to make sure it meets the requirements, date type, and constraints of the table. For dates, you can use the TO_DATE function when inserting date values.

INSERT INTO Customers (registration_date) VALUES (TO_DATE(’09-JAN-2025′, ‘DD-MON-YYYY’));

• Use transactions to make sure the data is consistent and reliable and that your database can recover from any errors or failures.
• Leverage tools like Tricentis to validate your database and catch errors before they affect customers.

Check the table´s constraints and data types before executing the inserts

Troubleshooting Common INSERT Issues

When working with your databases, here are some issues you could encounter:

• Error due to the table’s constraints and data types.
  Solution: Check the table’s constraints and data types before executing the insert.
• Using Oracle SQL reserved keywords like “NUMBER” or “BEGINS” as column names.
  Solution: Rename the column or use double quotes in queries like “NUMBER.”
• The number of columns in the INSERT statement don’t match the VALUES clause.
  Solution: Verify the column count and order.

Be sure to follow the INSERT rules:

• Numeric data: no delimiters
• Strings and dates: use single quotes
• Null values: write them as NULL
• Separate values with commas

Final Thoughts

This guide covered Oracle SQL INSERT techniques and troubleshooting tips. Inserting data is only part of the solution; maintaining data integrity over time is crucial. Tools like Tricentis can help you ensure accuracy, streamline workflows, and achieve your goals faster. Tricentis achieves this by automating tests that verify data accuracy after inserts, enforcing quality standards, and identifying discrepancies during system integrations. This reduces manual testing efforts and helps you feel confident about your data.

Request a demo today to see how Tricentis can support your Oracle database needs!

This post was written by Ifeanyi Benedict Iheagwara. Ifeanyi is a data analyst and Power Platform developer who is passionate about technical writing, contributing to open source organizations, and building communities. Ifeanyi writes about machine learning, data science, and DevOps, and enjoys contributing to open-source projects and the global ecosystem in any capacity.