Data Manipulation

Data Manipulation

So far we have seen how to retrieve data from the database, but before we can do this, some data must be stored. In Chapter 6 we will see how to use tools like SQL*Loader for storing data, but the majority of inserts will come from using the SQL commands STORE, INSERT and UPDATE.

Whenever data is changed in the database, it must be done within a read write transaction. Fortunately, Oracle8 always starts a read write transaction, but to keep your changes don't forget to issue the COMMIT statement at the end of the transaction.

Storing

Rows are stored in the database using the INSERT statement. If you are going to store a value in every column, then the list of columns can be omitted.

INSERT INTO orders.location_codes_iot

VALUES ('TVP','Reading HQ');

However, if you plan to only insert data for some of the rows in the column, then each column must be specified. In the example below, the table actually has four columns, but we are only inserting data into two of them. The omitted values will be given a null value.

INSERT INTO orders.order_header

(order_number, order_date)

VALUES ('100005',sysdate);

Another useful tip is that the values to be inserted can come from either another table or the same table. This technique can be very useful if anyone is trying to create some test data and needs to populate a table.

INSERT INTO orders.order_header

SELECT * FROM orders.order_header;

154 rows processed.

In the...

< Previous Excerpt Next Excerpt >

Purchase This Book

Oracle8 on Windows NT

TABLE OF CONTENTS

Data Manipulation

Contact Preferences

This is embarrasing...

Customize Your GlobalSpec Experience

Select Your Free Newsletters

Industry Newsletters

Select Your Free Product Alerts

This is embarrasing...