Oracle:INSERT

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

Manipulating data

When creating a new table 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.

Commmitting 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 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 tbl_name [(col_name1, col_name2...)]

VALUES (value_1, value_2...)

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; 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 (section 4.4) 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; 1.2 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 18.  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. 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.