Inserting Data - Oracle

The syntax for an SQL insert statement is as follows:

insert into tablename values (somevalue, somevalue, ...);

Or

insert into tablename (columnname, columnname, ...) values (somevalue, somevalue, ...);

The difference between above two insert statements is the columnname part. In the first statement values will be inserted in the order of columns as specified during creation. Let's look at an example. If we want to insert the first row of our department table as specified earlier then the insert statement would be:

insert into department values ('Sales',1,1);

Our department database table would look like this after this insertion.

insert into department values (1,'Sales',1);

Now if we use above statement, it will cause an error. Oracle is expecting the first element to be Dept, a varchar2, but it gets a number, 1. We can use the statement below to make Oracle think like us.

insert into department (DeptID, Dept, EmpID) values (1,'Sales',1);

It is better to mention the column names also, since sometimes we don't know the order of the columns.