Thursday, October 16, 2008

sql query to insert multiple rows - Oracle

Sql optimiser creates a perfect execution plan according to the query written by user.
The way the query is written logically helps in performance of an application.
Suppose there is a requirement to insert multiple records from a table to another table or a set records retrieved from a result set after running "select" query to a table.
Usually we start developing source code as
1) Retrieves records from select statement to a data table (Front end or application server memory) using data set or oracle reader.
2)Loop through the rows and read the field values into a variable .
3) Now run a sql query that inserts these field values creating a new record at destination table.

When the number of rows that are to be inserted are increased this definitely degrades the performance of the application.

Also Instead of spending more time in constructing these steps, it is reliable to build an effective sql query that does this task.

When a "select" sql auery is runned in oracle or sql , the result set is always a table, also ac coring to our example the destination is a table into which we need to insert records.
Ex:-
select '1' as num from dual;
This query returns as table which has one column (num) and one row (1).

So this table can be used to insert a single record into a table which has one column and of type number.

That is
insert into destination_table
(select '1' as num from dual);
This is just a simple example, where as in real time we may have multiple tables which result in a single table after running sql query and can be inserted into a single oracle table.

No comments: