Oracle:INSERT

Main Page >> Oracle and SQL >> Workbook >> DML >> 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...)

Add a new department:

INSERT INTO DEPT
   (DEPTNO, DNAME, LOC)
   VALUES (80,’HR’,’TELFORD’);
COMMIT;

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;

Exercise 3.2

3.2.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.

Remember the order of the values inserted must match the order of the columns in the EMP table. If you can not remember what this is, use DESC EMP first.

The system should respond with the following messages:

 1 row created.

 Commit complete.

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.

Inserting Times

Oracle's DATE data type is really a DATETIME. If you specify a date value without a time component, then the default time is midnight. Incidentally if you specify a date value without a date, then the default date is the first day of the current month.

To work with the TIME element you need to use the TO_DATE and TO_CHAR functions and provide a date format:

• TO_DATE is used to add a new date with a time element
• TO_CHAR is used to retrieve the date showing the time element.

Do note, even if you add a time element the SELECT statement will still default to showing the date only.

For example to see the current times in the Emp table,

SELECT empno, TO_CHAR(hiredate,'DD/MM/YY HH24:MI') AS hiredate FROM Emp;

To add a new employee that started at 1pm on the 1st December:

INSERT INTO Emp VALUES (9090,'TIME TEST','MANAGER', NULL, TO_DATE('01-DEC-2018 13:00', 'DD-MON-YYYY HH24:MI'),1000,NULL,10);

Don't forget to commit it:

COMMIT:

DD/MM/YY HH24:MI and DD-MON-YYYY HH24:MI are examples of date formats. HH24 means you are using the 24 hour clock.

Check that the time is correct:

SELECT empno, TO_CHAR(hiredate,'DD/MM/YY HH24:MI') AS hiredate FROM Emp
WHERE empno = 9090;

Exercise 3.3

When inserting data, you must not violate any of the constraints, or definitions setup for the schema. Why would the following INSERT statements violate the schema definition (you may want to look at the setup code for clues):

3.3.1 INSERT INTO CUSTOMER VALUES ('2000',NULL, 'K WEBSTER', 'MANCHESTER','ENGLAND', 6, 'WHEEL', 0);

3.3.2 INSERT INTO EMP (EMPNO, ENAME, HIREDATE, DEPTNO) VALUES (7955, 'HARPER', ’11-JUL-2000’, 30);

3.3.3 INSERT INTO DEPT VALUES (100, 'computing', 'MAIN SITE');

3.3.4 INSERT INTO EMP (EMPNO, ENAME, HIREDATE, DEPTNO) VALUES (7810, 'JONES', ’01-DEC-1999’, 50);

Note, since these all generate error messages, there is no need to commit/rollback the transaction.

Next Step

Deleting records.