How to Write Insert Statement In Oracle

How to Write Insert Statement In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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');

oracle database management system oracle performance tuning oracle insert oracle insert in oracle sql insert in oracle sql oracle dba oracle sql learn sql

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;

oracle database management system oracle performance tuning oracle insert oracle insert in oracle sql insert in oracle sql oracle dba oracle sql learn sql

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);

oracle database management system oracle performance tuning oracle insert oracle insert in oracle sql insert in oracle sql oracle dba oracle sql learn sql

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;

oracle database management system oracle performance tuning oracle insert oracle insert in oracle sql insert in oracle sql oracle dba oracle sql learn sql

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*