How to insert data in table in oracle?

Hi friend, I am here once again to discus about insert data in oracle table. When we talk about inserting data in table in oracle then we found that there is many way to insert. All are discuses here. Now lets start with inserting simple data in database.
"INSERT" command is used to insert data in database table. Using "INSERT" command we can insert single or multiple rows. First we see how to insert single row using "INSERT" command.

INSERT command

Insert single row

Syntax:

INSERT INTO <table_name> VALUES (v1,v2,v3,....);

Before inserting some point is there that we must need to remember.

• Strings datatype variables are enclosed with single quotes ( ' ' ).
• Date datatype variables also enclosed with single quotes( ' ' ).
• Numbers datatype variables must only number, no any other character.

Example
INSERT INTO emp VALUES (100, 'A', 'Clerk', 3000, '24-AUG-2015');

If we inserting today date on the place of data then we can also use a function called "SYSDATE". And we must not enclose it with quotes. Because this is not a date, this is a function.

INSERT INTO emp VALUES (101, 'B', 'Manager', SYSDATE); 

Insert single row in database table

Now if we wants to install data in some field only, not in all field. Then what to do? As we know that every cell of table must have data. Then on that case we can insert Null value at the place of missing data or empty cell. Actually Some people think that Null means zero(0). But they are wrong. Actually null is not equal to zero(0) or space. Null means blank/empty/missing data etc. Null is inserted when value is absent or unknown. Nulls can be inserted in two ways:

1. Explicitly (by user)
2. Implicitly (by oracle)

Now lets talk about both one by one.

Explicitly null insertion in oracle

For inserting null value in oracle explicitly (by user) we need to remember these two think:

• To insert null value in to numeric field we use "NULL".
• To insert null value in to character or date column we use just '' (open and juts closed single quotes).

Now lets see an example:

Explicitly insert null value in table

Implicitly null insertion in oracle

For inserting null value into table implicitly (bu oracle) we must to tall oracle in which field we inserting data, rest field will be field by null automatically by oracle. Lets first see the syntax:

Syntax:

INSERT INTO <table_name> (col1, col2, col3, .....) VALUES (v1, v2, v3, ......);

Example:

INSERT INTO emp (empno, ename, hiredate) VALUES (104, 'D', SYSDATE);

 

Implicitly insert null value in table

Now lets talk about inserting multiple rows in database table. Here we insert one record by writing query. If we need to insert like 1000 record then are we need to write query 1000 times? No, we need not to write query 1000 times. Just we can write query once and use it multiple time. 

Inserting multiple rows in table in oracle 

We can execute "INSERT" command multiple time with different values by using substitution. Substitutetable variable must prefix with "&". Lets see the syntax.

Syntax:

INSERT INTO <table name> VALUES (&clo1, &clo2, &col3, ....); 

If we enter / (forward splash) at SQLPlus command prompt then it will re-execute previous command. Lets see an example:

Example:

INSERT INTO emp VALUES (&empno, &emane, &job, &sal, &hiredate); 

Inserting multiple rows in table

We can also insert multiple rows with null value. In this also two ways: implicitly and explicitly. Lets see the example of both one by one:

Inserting multiple value with null (Explicitly)

Inserting multiple value with null (Implicitly)

Inserting multiple value with null (Explicitly)

For now that's all. For more information keep visiting "Oracle Gang"......