EnterpriseDB: INSERT COMMAND Oracle Tips by Burleson Consulting

The INSERT command is the command that allows you to add data to your tables.  In its most simple form, the insert command takes a list of columns followed by a list of data that maps to those columns.

The INSERT command follows one of these two basic formats:

INSERT INTO <table>
   (<column(s)>)
VALUES (<column values>);

Or:

INSERT INTO <table> [(<column(s)>)] <query|expression>;

You saw an example above of a very simple insert statement when we added data to the pocket tables: 

INSERT INTO left_table (amount) VALUES (.25);

This command says to add a record (insert) to the left_table table and put the value of .25 in the column amount.  The parenthesis (()) around the column list (amount in this example) and around the values (.25 in this example) are required.

Another form of the insert command is the INSERT SELECT command:

INSERT INTO left_table (amount) (SELECT amount FROM right_table);
COMMIT;
INSERT INTO left_table (SELECT * FROM right_table);
COMMIT;

edb=# INSERT INTO left_table (SELECT * FROM right_table);
INSERT 0 6
edb=# INSERT INTO left_table (amount) (SELECT amount FROM right_table);
INSERT 0 6
edb=# COMMIT;
COMMIT
edb=# INSERT INTO left_table (SELECT * FROM right_table);
INSERT 0 6
edb=# COMMIT;
COMMIT
edb=#

The first select listed the column that we wanted.  The second insert used a short hand that told the SQL engine that we wanted all of the columns.

When using INSERT, we can make function calls in the insert statement itself.  For example, if we wanted the amount field in left_table to be a sequence value, we could assign the sequence to a variable and then insert the variable.  Rather than going through all of that work, we can just put the sequence directly in the command:

INSERT INTO left_table (amount) VALUES (seq1.nextval );

edb=# INSERT INTO left_table (amount) VALUES (seq1.nextval );
INSERT 0 1
edb=#

You can also put expressions in your insert:

INSERT INTO left_table (amount) VALUES (9 + 4 );

edb=# INSERT INTO left_table (amount) VALUES (9 + 4 );
INSERT 0 1
edb=#

You can also use expressions in your INSERT SELECT:

INSERT INTO left_table (amount) (SELECT ((amount  + 5) / 2) FROM right_table);

edb=# INSERT INTO left_table (amount) (SELECT ((amount  + 5) / 2) FROM right_table);
INSERT 0 6
edb=#

That's just about all there is for the insert command.  Next up is the UPDATE command.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.