Oracle:INSERT

Main Page >> Oracle and SQL >> Workbook >> Inserting data

Manipulating data

When a new table is created it will contain no rows, unless you have taken steps to copy data from another source. This section looks at how to add data and save the changes.

Committing the changes

SQL supports Data Manipulation Commands (DML) to allow data to be added, updated, or deleted. Normally changes to a database e.g. inserts, deletes and updated do not take an immediate permanent affect. Such changes would form a transaction and the effects of the commands have to be saved, or rejected.

Transactions are sets of modifications that must either all succeed, or else all fail. For example, given a transaction to raise every employee’s salary by 10%, everyone should get the increase, not only say half if a power failure happened half way through the transaction.

Another transaction could be placing an order for 2 items of goods on Amazon, which involves 1 INSERT into an ORDER_HEADER table, 2 INSERTS into an ORDER_LINES table and 1 UPDATE to a CUSTOMER profile table to amend your credit available amount. The 3 INSERTS and 1 UPDATE will form a transaction, that should be saved all together, or not at all.

Normally a user applies all the changes they want in a transaction and then if they are happy with the results makes them permanent. Otherwise they restore the database to the state it had before any changes were made.

The command to make changes permanent is:

COMMIT

The command to reverse all the changes since the last commit is:

ROLLBACK

COMMIT is like pressing File>Save in a Word document, whilst ROLLBACK is like quitting Word without saving your changes.

Remember to type COMMIT after each insert/delete or update if you want to keep the change(s) made.

The system will automatically commit your records if you carry out any data definition commands, such as a CREATE TABLE statement, or if you exit the system cleanly (that is, you quit the system properly rather than just closing the SQL*Plus window).

Adding Data

To add a new row of data involves the INSERT command:

INSERT INTO table_name [(col_name1, col_name2...)] VALUES (value_1, value_2...)

Including the column names is optional, as seen in the examples below.

To add a new employee to the EMP table type

INSERT INTO EMP
   VALUES (7945, 'KING','CLERK',7698,'7-APR-1999',1000,NULL,30);
COMMIT;

If the column names are not included, the values for the fields must be in the order in which they were defined in the table. Otherwise, incorrect or invalid data will be inserted in the wrong fields and can cause an error message. If the order is unknown use the DESCRIBE command to check.

To verify the information you have added, type:

SELECT * FROM EMP
WHERE ENAME = 'KING';

Insert a new department:

INSERT INTO DEPT
   VALUES (90,’FSE’,’WOLVERHAMPTON’);
COMMIT;

Inserting rows with null values

If you want to leave several fields in a table blank, it may be quicker to specify the fields to be filled in rather than specifying NULL for the blank fields, e.g.

INSERT INTO EMP
  EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)
  VALUES (7955,'WILSON','CLERK','22-APR-1988', 1500, 30);
COMMIT; 

Remember that the DBMS will give you an error message if you attempt to assign a null value to a field that must have a value entered. The above example will work, as the database knows that the missing commission attribute COMM is allowed to hold a null value.

Numeric values do not require quotes round them, however character and date fields must be enclosed in single quotes ('). The normal default format for the date field is ‘DD-MON-YY', though other formats can be used with the TO_DATE function, see section xx.

Alternatively, if you know the exact order of the fields, it is probably quicker to insert null values for any fields with missing values. To leave any field blank, use the value NULL:

INSERT INTO CUSTOMER 
  VALUES ('1111', NULL, 'A LEMMING', 'MANCHESTER', 'ENGLAND', NULL, NULL, NULL);
COMMIT; 

A point to ponder on, if the customer 1111, A Lemming, makes a lot of orders, is it a good idea to constantly type in his details? Can you think of any disadvantages of placing the Customer and Part details in one table? Plus, how would you represent a new customer who has yet to place an order, or new parts that have yet to be ordered by anyone? Keep this in mind when we look at database design in the lectures.

Multiple Inserts

Whilst an INSERT statement only allows you to insert a record to one table at a time, the INSERT ALL command allows multiple inserts into either one or more tables.

For example, to add 3 new records to the DEPT table:

INSERT ALL
   INTO Dept VALUES (15, 'MARKETING', 'BIRMINGHAM')
   INTO Dept VALUES (25, 'ART AND DESIGN', 'LONDON')
   INTO Dept VALUES (35, 'TEACHING', 'WALSALL')
SELECT * FROM DUAL;
COMMIT;

Note, the SELECT * FROM DUAL is part of the syntax and the above will not work without it.

To add 2 new records to the DEPT and EMP tables:

INSERT ALL
   INTO Dept VALUES (45, 'SCIENCE', 'WOLVERHAMPTON')
   INTO EMP (empno, ename, job, hiredate, sal, deptno) 
  VALUES (8888,'JAMES','TEACHER', '01-JAN-2014', 2000, 35) 
SELECT * FROM DUAL;
COMMIT;

Note, you cannot set the deptno for a new emp record to department that did not previous exist. This is to do with the referential integrity constraints. Constraint checking can be deferred to commit time and this is covered in more advanced database modules.

Exercise 4.1

4.1 Now create a new EMP record with the following values – EMPNO: 9000, ENAME: your surname, JOB: MANAGER, DEPTNO: 90, COMM: 0, MGR: 7839, HIREDATE: today's date and give yourself a value for SAL.

The system should respond with the following messages:

 1 row created.

 Commit complete.

Next Step

Deleting records.