INSERTING DATA INTO TABLE

Using Merge Command
As of oracle9i, you can use the merge command to perform inserts and updates in to a single table in a single command. Based on the conditions you specify, oracle will take the source data i.e. a table, view, or a query and update existing values if the conditions are met. If the conditions are not met, the row will be inserted.

For example, create a duplicate table of emp with name emp1 and make some changes to emp1. now we can merge the changes made to emp1 in to emp by using merge command as follows.


Merge Into Emp EUsing
(Select Empno,Ename,Job,Mgr,Hiredate,Sal,Comm,Deptno From Emp1) E2On(E.Empno=E2.Empno)
When Matched Then
Update Set E.Sal=E2.Sal,E.Comm=E2.CommWhen Not Matched Then Insert values(E2.Empno,E2.Ename,E2.Job,E2.Mgr,E2.Hiredate,E2.Sal,E2.Comm,E2.Deptno)


Inserting Data Into A Table


After creating the table we can insert data into that table by using insert statement. Insert statement has following syntaxes.

Syntax 1: Insert into tabname values( val1, val2, val3, . . . , val n );
Example: Insert into Student values( 1, ‘SAI’, ‘Apet’, ‘ORACLE’, 2000);

Syntax 2: Insert into tabname values( &col1, &col2, &col3, . . . , &co n );
Example : Insert into Student values( &Sno, ‘&Sname’, ‘&Address’, &Fee );

Second syntax will be easier for user when he/she has to insert serveral rows in to the table. By using above two syntaxes we must provide values for all columns of the table even columns don’t have ‘Not Null’ constraint. To insert data into a table by excluding some columns, the following syntaxes are used.

Syntax 3: Insert into tablename(Col1, Col2, Col3,. . .,Col n) values ( Val1, Val2, Val3, . . . ,Val n);
Example: Insert into Marks ( Sno, M1, M2, M3 ) values ( 1, 55, 67, 82 );


Syntax 4: Insert into tablename (Col1, Col2, Col3, ..., Col n) values (‘&Col1’, ‘&Col2’, ‘&Col3’, ... , ‘&Col n’);
Example: Insert into Marks (Sno, M1, M2, M3) values (&sno,&m1,&m2,&m3);

Insert With Select

You also can insert information that has been selected from a table.

Syntax : Insert into Tablename select col1, col2, col3, … , col n from tablename;





Using Append Hint

Oracle uses an optimizer to determine the most efficient way to perform each SQL command. For insert statements, 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. However, it may not provide adequate performance for an insert with a select command that inserts multiple rows.you can override the execution plan by using the append hint to improve the performance of large inserts.the append hint will tell the database to find the last block into which the table’s data has ever been inserted. The new records will be inserted starting in the next block following the last previously used block. Since the data is being written into new blocks of the table, there is much less space management work for the database to do during the insert. Therefore, the insert may complete faster when the append hint is used.

You specify the append hint within the insert command. A hint looks like a comment. It starts with /* and ends with */. The only difference is that the starting set of characters includes a + before the name of the hint.

Syntax : insert /* + APPEND */ in to tablename select statement.

Since the new records will not attempt to reuse available space that the table has already used, the space requirements for the table may increase. In general you should use the append hint only when inserting large volumes of data in to tables with little reusable space. The point at which appended records will be inserted is called the table’s high-water mark.

Multitable Inserts

As of oracle 9i, you can perform multiple inserts in a single command. You can perform all of the inserts unconditionally or you can spcify conditions using a when clause to tell oracle how to manage the multiple inserts. If you specify all, then all of the when clauses will be evaluated. Specifying first tells oracle o skip subsequent when clauses after it finds one that is true for the row being evaluated. You can also use an else clause to tell oracle what to do if none of the when clauses evaluates to true.


Syntax : Insert [ALL] / [FIRST]
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
[When condition]
Into tablename values(val1, val2, …, val n)
.
.
.
else
Into tablename values(val1, val2, …, val n)
Select statement;