The SQL command insert lets you place a row of information directly into a table.
SQL> DESCRIBE comfort;
Name Null? Type
City VARCHAR2(13)
Sampledate DATE
Noon NUMBER
Midnight NUMBER
Precipitation NUMBER
SQL> INSERT INTO comfort VALUES ('WALPOLE', TO_DATE('21-MAR-1999','DD-MON-YYYY'), 56.7, 43.8, 0);
1 row created.
ð The word values must precede the list of data to be inserted. A character string must be in single quotation marks. Numbers can stand by themselves. Each field is separated by commas and the fields must be in the same order as the columns are when the table is described.
You can also insert a NULL. This simple means the column will be left empty for this row, as shown in the following:
SQL> INSERT INTO comfort (SampleDate, Precipitation, City, Noon, Midnight)
VALUES (TO_DATE('21-MAR-1999','DD-MON-YYYY'), NULL, 'WALPOLE', 86.3, 72.1);
1 row created.
SQL> INSERT INTO studentinfo VALUES (&id,'&firstname','&lastname',TO_DATE('&admitdate','DD-MON-YYYY'),'&course' );
Enter value for id: 00002
Enter value for firstname: Shovon
Enter value for lastname: Roy
Enter value for admitdate: 02-JUN-2005
Enter value for couese: Economics
old 1: insert into studentinfo values (&id,'&firstname','&lastname',TO_DATE('&admitdate','DD-MON-Y
new 1: insert into studentinfo values (00002,'Shovon','Roy',TO_DATE('02-JUN-2005','DD-MON-YYYY'),'
1 row created.
SQL> /
ð It will repeat the previous command again to help operator to work quickly.
SQL> INSERT INTO comfort (SampleDate, Precipitation, City, Noon, Midnight)
SELECT TO_DATE('22-DEC-1999','DD-MON-YYYY'), Precipitation, 'WALPOLE', Noon, Midnight FROM comfort
WHERE City = 'KEENE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row created.
ð You can't use the insert into .... select from syntax with LONG datatypes unless you are using the TO_LOB function to insert the LONG data into a LOB column.
SQL> SELECT * FROM comfort WHERE City = 'WALPOLE';
CITY SAMPLEDATE NOON MIDNIGHT PERCIPITATION
WALPOLE 21-MAR-99 56.7 43.8 0
WALPOLE 22-JUN-99 56.7 43.8 0
WALPOLE 23-SEP-99 86.3 72.1
WALPOLE 22-DEC-99 -7.2 -1.2 3.9
An Aside About Performance
For insert statement, Oracle tries to insert each new record into an existing block of data already allocated to the table. This execution plan optimizes the use of space required to store the data. You can override the execution plan by using the APPEND hint to improve the performance of large inserts.
SQL> INSERT /*+ APPEND */ INTO WORKER (Name)
SELECT Name FROM Prospect;
ð The records from the prospect table will be inserted into the worker table. Instead of attempting to reuse previously used space within the worker table, the new records will be placed at the end of the table's physical storage space. In general, you should use the APPEND hint only when inserting large volumes of data into tables with little reusable space.
Rollback, Commit and Autocommit
When you inset, update or delete data from the database, you can reverse or roll back the work you've done. This can be very important when an error is discovered. The process of committing or rolling back work is controlled by two SQL PLUS commands, commit and rollback. This is controlled by the autocommit feature of set. Like other set features, you can show it, like this,
SQL> SHOW AUTOCOMMIT;
autocommit OFF.
ð OFF is the default. You can also specify a number for the autocommit value; this value will determine the number of commands after which Oracle will issue a commit.
SQL> COMMIT;
Commit complete.
ð Until you commit, only you can see how your work affects the tables. Anyone else with access to these tables will continue to get the old information.
You can do quite a large number of inserts, updates and deletes and still undo the work by issuing this command:
SQL> ROLLBACK;
rollback complete.
ð However, the message 'rollback complete' can be misleading. It means only that Oracle has rolled back any work that hasn't been committed.
If you've complete a series of inserts, updates or deletes, but have not yet explicitly or implicitly committed them and you experience serious difficulties, such as a computer failure, Oracle will automatically roll back any uncommitted work.
Delete
Removing a rows from a table requires the delete command. The where clause is essential to removing only the rows you intend. delete without a where clause will empty the table completely.
SQL> DELETE FROM comfort WHERE City = 'WALPOLE' ;
4 rows deleted.
SQL> SELECT # FROM comfort WHERE City = 'WALPOLE' ;
no rows selected.
Now rollback the delete an run the same query:
SQL> ROLLBACK ;
rollback complete.
SQL> SELECT # FROM comfort WHERE City = 'WALPOLE' ;
CITY SAMPLEDATE NOON MIDNIGHT PERCIPITATION
WALPOLE 21-MAR-99 56.7 43.8 0
WALPOLE 22-JUN-99 56.7 43.8 0
WALPOLE 23-SEP-99 86.3 72.1
WALPOLE 22-DEC-99 -7.2 -1.2 3.9
This illustrates that recovery is possible, so long as a commit hasn't occurred.
An additional command for deleting records, truncate, does not behave the same as delete. Whereas delete allows you to commit or rollback the deletion, truncate automatically deleted all records from the table. The action of the truncate command cannot be rolled back or committed; the truncated records are unrecoverable.
Update
Update requires setting specific values for each column you wish to change and specifying which row or rows you wish to affect by using a carefully constructed where clause.
SQL> UPDATE comfort SET Precipitation = .5, Midnight = 73.1
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
Note: Always set feedback on when doing updates, and look at the feedback to be sure the number of rows updated is what you expected it to be.
Update with Embedded Select
It is possible to set value in an update by embedding a select statement right in the middle of it. Note that this select has its own where clause, picking out the temperature from the City of MANCHESTER from the WEATHER table, and the update has its own where clause to affect just the City of WALPOLE on a certain day:
SQL> UPDATE comfort SET Midnight = (
SELECT Temperature FROM weather WHERE City = 'MANCHESTER')
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.
You also can use an embedded select to update multiple columns at once.
SQL> UPDATE comfort SET (Noon, Midnight) = (
SELECT Humidity, Temperature FROM weather WHERE City = 'MANCHESTER')
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.
Update with NULL
You also can update a table and set a column equal to NULL.
SQL> UPDATE comfort SET Noon = NULL
WHERE City = 'WALPOLE' AND Sampledate = TO_DATE('22-DEC-1999','DD-MON-YYYY');
1 row updated.