Manipulating with the Data

After creating the table the data is entered using the Data manipulation statements. The Insert command is used to insert new records, the delete command is used to delete records based on certain conditions and the update command replaces the old value with the new value.

Using INSERT Command

The main work of Insert command is to add more or one rows to a table. The values are separated through commas and the values are entered in the similar ORDER as specified through the structure of the table. The Inserting records into tables can be done in variant ways:
•    Inserting records in the all fields
•    Inserting records in the selective fields
•    Continuous Insertions
•    Inserting records using SELECT statement

Case 1:

Let Consider inserting records onto all the fields in the table.

Syntax:

Insert into <tablename> Values (value1, value2, value3…);

In the syntax the number of values must correspond to the number of columns in the table.

Example:

INSERT INTO Employee VALUES (1237,’kalai’,’10-MAR-2000’, 5000); the message displayed will be:
1 row created.

This command inserts the record where the employee number is 1000; his name is Jack and many more. Always character data must be entered within single quotes.

In the above instance, the column known as doj holds a date datatype. Although inserting date values, the values have to be enclosed within quotes. The typical format of entering the date values is ‘DD-MON-YY’.
Note: Any value other then more number must be placed within single quotes. Elsewhere it treats the value as a column name.

Case 2:

Let Consider inserting Values into Selective fields

Syntax:

Insert into <tablename>(selective column1, selective column2) Values (value1, value2);

Example:

INSERT INTO Employee (empno, empname) VALUES (1330,’saravanan’);
Displays the feedback as
1 row created.

Case 3:

Let Consider continuous insertion of records.  In order to insert continuously using of “&” (ampersand).

Syntax:

Insert into <tablename> Values (&col1, &col2, &col3…);
Oracle prompts the user to insert values onto all the columns of the table. The following example describes this.

Example

INSERT INTO employee VALUES (&eno,'&name', '&doj',&sal);
Output will be:
Enter value for eno: 1247
Enter value for name: Meena
Enter value for sal: 5000
Enter value for doj: 10-jan-2000
old 1: insert into employee values (&eno,'&name', &sal, &doj)
new 1: insert into employee values (1247,'meena', 5000, '10-jan-2000')
1 row created.

Here, alternate of enclosing the character values in quotes each time, it is enough if it is given although using the Insert command. Now the similar command can be re-executed through providing / (slash) as long as this is the newest command that is there in the buffer.

Now, let consider inserting records continuously for selective fields. This is same to case 2. Insert into <tablename>(selective column1, selective column2) Values (&col1, &col2);

The following instance inserts records into the empname and empno columns.

Example:

INSERT INTO Employee (empno, empname) VALUES (&eno,’&name’); Output
Enter value for eno: 1440
Enter value for name: Diana
old 1: insert into employee values(&eno,'&name')  # old query for employee selection #
new 1: insert into employee values(1440,'Diana') # new query for employee selection #
1 row created.

Case 4:

Multiple Records can be inserted by using a single Insert command along with Select statement. That case is dealt after the section on Select Statement.

Note: By using Insert and Values combination, only one record can be inserted at a time.