Insert Command in Oracle

Insert command:

Inserting rows into a table

 SQL command INSERT is used to insert new row into the table. While inserting rows, you may enter value for each column of the table or selected columns. The following command inserts a row into COURSES table.

Syntax: insert into courses values('ora','Oracle database',25,4500,'Knowledge of Windows');


Note: After inserting the required row, issues COMMIT command to make sure the changes are made permanent.  Without COMMIT, rows that are inserted might be lost if there is any power failure. During insertion, character values are enclosed in single quotes. Unless otherwise specified we have to supply a value for each column of the table. If the value of any column is not known or available then you can give NULL as the value of the column. For example, the following insert will insert a new row with null value for PREREQUISITE column.

insert into courses values('c','C Programming',25,3000,null);

Note: INSERT command can insert only one row at a time. For multiple row, INSERT command must be issued for multiple times. DATE type values must be in the format DD-MON-YY or DD-MON-YYYY, where MON is the first three letters of the month (Jan, Feb). If only two digits are given for year then current century is used. For example, if you give 99 for year, Oracle will take it as 2099 as the current century is 2000. So it is important to remember this and give four digits if required. The following is the complete syntax for INSERT command.

INSERT INTO tablename [(columns list)] {VALUES (value-1,...) | subquery }


Inserting a row with selected columns:
It is possible to insert a new row by giving values only for a few columns instead of giving values for all the available columns. The following INSERT command will insert a new row only two values.

insert into courses(ccode,name) values ('odba','Oracle Database Administration');

The above command will create a new row in COURSES table with values for only two columns CCODE and NAME. The remaining columns will take NULL value or the default value, if the column is associated with default value.