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.

Commmitting the changes

SQL supports Data Manipulation Commands (DML) to allow data to be added, updated, or deleted. Such changes would form a transaction and the effects of the commands have to be saved, or rejected.

Normally changes to a database e.g. inserts, deletes and updated do not take an immediate permanent affect. Modifications are assumed to be part of a transaction.

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.

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