How to Write Insert Statement In Oracle

This tutorial page is based on examples to make it easier to follow. The Oracle INSERT statement allows to add a row or many rows into Oracle database table or into other types of Oracle objects. The syntax of Oracle INSERT statement is:

INSERT INTO <table_name> (<columns>)
     VALUES (<values>);

For example we have table CUSTOMERS that has two columns FORENAME and SURNAME. The Oracle DBA will add into that table a new client named “John Lennon”. To do it, the Oracle INSERT statement would look as following:

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

The example output above shows that we did insert 1 line, but the line will be lost with the next log on to Oracle database. To save the line permanently to the database table you need to run command COMMIT after your statements are done as it shows on the following example.

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

COMMIT;

Another way to do INSERT is to take your lines from another Oracle database table. You can fetch them using a Select statement. The Oracle INSERT with the select statement syntax is :

INSERT INTO <table_name> (<columns>)
      (SELECT <columns> FROM <table names> WHERE <conditions>);

Now we have a second example with the INSERT that takes lines from another Oracle table named CUSTOMERS_BACKUP and inserting them into the CUSTOMERS table in Oracle database.

INSERT INTO customers (forename, surname)
      (SELECT ocu.forename, ocu.surname FROM customers_backup ocu);

And to store the change you would need to use the commit command again.

While you are doing insert you can receive back just inserted values by using keyword RETURNING. This keyword is quite handy for new ID values or other values generated during the inserting process. The next example shows a forename value returned by the Oracle INSERT statement. To output the returned value we are going to use a bind variable.

VARIABLE v_forename CHAR(200);

BEGIN
  INSERT INTO users_backup (forename,surname)
       VALUES ('John','Lennon' ) 
    RETURNING forename INTO :v_forename ;
END;
/

print v_forename;

As you see in the Oracle INSERT statement above we did insert name “John Lennon” and since we asked to return only forename the :v_forename bind variable shows value John in its output.

See Also:
Oracle Select Oracle Dynamic SQL with Bind Variables Oracle Rownum Home