INSERT multiple rows with single SELECT statement - INSERT ALL

 
We might face the requirement to INSERT multiple records in a table; where we have either go for SQL Loader, CSV upload  or  Multiple INSERT statements. 

We have another simple option in ORACLE;  which is INSERT ALL..


Consider the table as example. 

TABLE NAME : DOMAIN

 Name             Type
 ---------------- ------------
 DOMAIN_NAME    VARCHAR2(30)
 REGION              VARCHAR2(10)

Consider the requirement to INSERT the below data 

DOMAIN_NAME       REGION
---------------- --------
D001                   APAC
D002                   EMEA
D003                   US
D004                   APAC
D005                   US

Using INSERT statements:-

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D001', 'APAC' );

COMMIT;

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D002', 'EMEA' );

COMMIT;

 

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D003', 'US' );

COMMIT;

 

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D004', 'APAC' );

COMMIT;

INSERT into DOMAIN (DOMAIN_NAME, REGION) values ('D005', 'US' );

COMMIT;

Think about if this requirement is for inserting more number of records. 

We need to write and execute "N" number of INSERT statements.

GOOD NEWS IS.. we can achieve the same by using single INSERT statement.

Using INSERT ALL statement:-

INSERT ALL 

into DOMAIN(DOMAIN_NAME, REGION) values('D001', 'APAC' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D002', 'EMEA' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D003', 'US' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D004', 'APAC' ) 

into DOMAIN(DOMAIN_NAME, REGION) values('D005', 'US' ) ;

COMMIT;

With respect to Performance; INSERT ALL is better, because parsing iteration to the table DOMAIN takes place for one time; whereas for INSERT statement, parsing takes place for multiple times.

MERGE Statement in Oracle - Syntax with examples

When we ask for what are the DML statements?, we used to say   SELECT, INSERT, UPDATE and DELETE.  But, we have one more special statement which is MERGE.  One of the most powerful DML SQL statement. 

Let us see today on  WHAT  is  /  HOW it works / SYNTAX of  / Example of  / BENEFITS of MERGE statement.



What is Merge statement first of all.. ?

MERGE statement is dynamic statement for situations; when you want to do any of the below:

           Update existing rows in a table [or]
           Insert new rows [or]
           Delete existing rows  

depending on a match condition.

Instead of achieving it by  many IF conditions and multiple Insert/Update/Delete statements, we can achieve this situation by using ONE MERGE statement. 



How MERGE statement works.. ?

To understand How MERGE statement works, let us have two tables  a) STUDENT   &   b)   STUDENT_1

SQL> select * from STUDENT;

        ID NAME              MARKS
---------- ------------ ----------
       103 Modi               650
       104 Pudin              620
       105 Trump              555

5 rows selected.

SQL> select * from STUDENT_1; 

        ID NAME              MARKS
---------- ------------ ----------
       103 Modi               690
       104 Putin              620
       105 Trump              555

       106 Talai              540

Now, we have a requirement to do the below actions. 

   a)  Mark of the student Modi to be corrected from 650 from 690

   b)  Name of the student Pudin to be corrected as Putin

   c)  New student Talai record to be inserted

   d)  No change for the student Trump

   

Can we achieve this in one single statement?.  Yes, by using MERGE. 

We will understand the syntax and arrive the SQL for this requirement below. 

MERGE statement SYNTAX :- 

MERGE into <target table>          /*  Refer Student table here*/
USING
    <source table/view/result of sub query>       /*  Refer Student_1 table here */
ON
    <match condition>
WHEN MATCHED THEN
    <update clause>          /* ANY DML */          
    <delete clause>          /* ANY DML */
WHEN NOT MATCHED THEN
    <insert clause>          /* ANY DML */

MERGE statement for the above example ( Student & Student 1 )

SQL> merge into student a
  2  using
  3    (select id, name, marks      
  4     from student_1) b
  5  on (a.id = b.id)
  6  when matched then
  7    update set a.name = b.name
  8         , a.marks = b.marks
  9  when not matched then
 10    insert (a.id, a.name, a.marks)
 11    values (b.id, b.name, b.marks);

The above highlighted yellow section is USING clause. Where we can use any of the below 

    a)  Result of select statement  ( as we used above )  OR

    b) Table name  OR

    c)  View name

Benefits of MERGE statement:

1. Can achieve multiple DML in ONE Merge statement
2. Can avoid looping through for each record 
3. Can avoid multiple IF conditions
4. Parsing through the tables for multiple times will be reduced to ONE

< Feel free to comment below with your feedback >

SQL Query to generate CALENDAR for Year 2017

SQL is not only an  " Art of Generating Data " ,  but also an " Art of Showing Data "

We all know about date functions in SQL,  can we do create a calendar for year 2017.. using Date functions .. and also for years .. 2016..2018...1823...2957.. ?

YES.. WE CAN..  using SQL..

First we will see the Query to show the CALENDAR for year  2017.

Try executing the below queries in your SQL developer or TOAD by just changing year as you want. 

Calendar SQL Statement for 2017 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-2017','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2

 

Oh.. I want to see my year of birth.. say for eg., 1991

Calendar SQL Statement for 1991 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-1991','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2

 

Around 1000 years before :-

Calendar SQL Statement for 1015 :-

WITH TT AS (SELECT (TO_DATE('01-Jan-1015','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2 

After 1000 plus year and so on..

Calendar SQL Statement for 3742 AD :-

WITH TT AS (SELECT (TO_DATE('01-Jan-3742','DD-Mon-YYYY')) DAT FROM DUAL )
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,  "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(DT,'fmMonthfm YYYY') MONTH,TO_CHAR(DT+1,'iw') WEEK,
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'1',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Sun",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'2',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Mon",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'3',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Tue",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'4',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Wed",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'5',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Thu",
NVL(MAX(DECODE(TO_CHAR(DT,'d'),'6',LPAD(TO_CHAR(DT,'fmdd'),2))),'  -') "Fri",
nvl(MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))),'  -') "Sat"
FROM ( SELECT TRUNC(tt.dat,'y')-1+ROWNUM dt, rownum
FROM SHIPMENT S, TT
WHERE ROWNUM <= ADD_MONTHS(TRUNC(TT.dat,'y'),12) - TRUNC(tt.dat,'y'))
GROUP BY TO_CHAR(DT,'fmMonthfm YYYY'), TO_CHAR( DT+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ) , 2 

 

##  Kindly feel free to comment your feedback  ##

How to Create and Use Oracle Database Link ( DB Link )

We of-course heard the keyword DB LINK from DBAs. Every developer should know about this to ease their day to day DB data handling tasks. 

Okay.. Let see in detail What is DB link ?, How to create DB link? and How perfectly use DB link?  in this article.  

About DB Link :-

Database Link is an object can be created in one database; by which we can access other database objects such as tables, views etc.,

For example :-  If we create DB Link in DEV DB to connect with QA DB, then we can access QA database from DEV DB.  We can do SELECT, INSERT, UPDATE & DELETE statements for QA tables from logging into DEV DB.

Prerequisites to create DB link :-

Database should have the below two accesses to create DB link 

          a)  CREATE DATA BASE LINK   ( at local DB )   

               to create private DB LINK

                     [OR]

               CREATE PUBLIC DATA BASE LINK ( at local DB )

               to create public DB LINK

               

          b)  CREATE SESSION ( at remote DB )

How to create DB link :-

SYNTAX 1  ( To create PRIVATE  DB  Link )

CREATE DATABASE LINK   DB_LINK_NAME  USING  SERVICE_NAME 

SYNTAX 2  ( To create PUBLIC   DB  Link )

CREATE PUBLIC DATABASE LINK   DB_LINK_NAME  USING  SERVICE_NAME

SYNTAX 3 (  To create PUBLIC  DB LINK to access particular schema  )

CREATE PUBLIC DATABASE LINK   DB_LINK_NAME  

CONNECT TO  SCHEMA_NAME

IDENTIFIED BY   PASSWORD

USING   SERVICE_NAME

DB_LINK_NAME  ->   Name to connect with remote DB

SERVICE_NAME  ->   Remote DB service name

SCHEMA_NAME   ->  User name of Remote DB schema name

PASSWORD         ->  Password to connect remote DB schema

How to use DB link :-

SELECT FROM TABLE_NAME@DB_LINK_NAME

DELETE FROM TABLE_NAME@DB_LINK_NAME

TABLE_NAME  ->  Table name of a remote DB 

How to see existing DB links in the current schema/DB :-

SELECT  *   FROM     DBA_DB_LINKS

( List all Database Links in the local database )

SELECT  *   FROM     ALL_DB_LINKS

( List all Database Links accessible to the current user )

SELECT  *   FROM     USER_DB_LINKS

( List all Database Links owned by current user )

How to Drop Data base link 

DROP DATABASE LINK  DB_LINK_NAME

DROP PUBLIC DATABASE LINK DB_LINK_NAME

( Think twice before you execute DROP DB LINK statement; since it will drop the access to other DB's object )

Your feedback about this article is most welcome. Please feel free to comment below. 

How to use IN and EXISTS in SQL effectively ?

Developers have this doubt in mind often on HOW and WHEN to use either IN or EXISTS.  Here is the solution.

Consider two tables - Shipment & Shipment Refnum

Shipment  ( Parent Table )
Shipment_gid    Varchar2
Domain_name   Varchar2


Shipment_Refnum ( Child Table )
Shipment_gid  Varchar2
Shipment_refnum_qual_gid   Varchar2
Shipemnt_refnum_value        Varchar2
Domain_name  Varchar2 


HOW to USE "IN"

When the INNER QUERY passes the direct output value to OUTER QUERY; we can use "IN" as below. 

SELECT *       /* outer query */
  FROM SHIPMENT S
 WHERE S.SHIPMENT_GID  IN
                                         ( SELECT    SHIPMENT_GID     /*  inner query */
                                                 FROM    SHIPMENT_REFNUM SR
                                             WHERE    SR.SHIPMENT_REFNUM_QUAL_GID = 'RQG'
                                                AND  SR.SHIPMENT_REFNUM_VALUE = 'SRV' )
AND S.DOMAIN_NAME = 'DUMMY';



The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.


HOW to USE "EXISTS"

The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.

This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

SELECT  *  FROM SHIPMENT S
                                               WHERE EXISTS
                                                                               (  SELECT  1  FROM SHIPMENT_REFNUM SR
                                                                                        WHERE SR.SHIPMENT_REFNUM_QUAL_GID = 'RQG'
                                                                                                AND  SR.SHIPMENT_REFNUM_VALUE = 'SRV'
                                                                                                AND SR.SHIPMENT_GID = S.SHIPMENT_GID )
AND S.DOMAIN_NAME = 'DUMMY';   

                     

WHEN to USE "IN"  &  "EXISTS"

The recommendation at that time were:

• If the majority of the filtering criteria is in the INNER query, use IN.
• If the majority of the filtering criteria is in the outer query, use EXISTS.

When the INNER query has the feasibility of returning very less number of records, IN is better.

When the INNER query has the feasibility of returning more number of records, EXISTS is better. 


In other words,

• IN for big OUTER query and small INNER query.
• EXISTS for small OUTER query and big INNER query.

How to identify a particular string in LOB (XML_BLOB) column data ?

Dear Friends, 

You might have come across the situation to check whether a particular string is existing anywhere within the LOB column value. Here we go with the solution.


Say for example:-

Consider the table I_TRANSACTION  which has the below columns 

I_TRANSACTION_NO    NUMBER, 
XML_BLOB                   CLOB, 
DOMAIN_NAME            VARCHAR2


And the value for XML_BLOB seems like below.

<TenderOffer><Shipment><ShipmentHeader><ShipmentGid><Gid><DomainName>DUMMY</DomainName><Xid>SHIP12345</Xid></Gid></ShipmentGid><ShipmentRefnum><ShipmentRefnumQualifierGid><Gid><Xid>BM</Xid></Gid></ShipmentRefnumQualifierGid><ShipmentRefnumValue>.........
........
........
......../Gid></ShipmentGid></ShipmentStatus></Shipment><ExpectedResponseDt><GLogDate>20170216121036</GLogDate>


Requirement:-

If you want to identify whether the particular key value  "SHIP12345"  is available or not ?  

DBMS_LOB.INSTR function can be used to achieve this. 


Oracle Syntax :-

There are four parameters to be passed as below.
DBMS_LOB.INSTR   (   COLUMN_NAME,   VALUE_TO_SEARCH,    START_POSITION_SEARCH,  NTH_TIME_OF_OCCURRENCE ) 

Example Query:-

SELECT  DBMS_LOB.INSTR(   XML_BLOB,   'SHIP12345',   1,  1 ) 
FROM I_TRANSACTION
WHERE I_TRANSACTION_NO = 12345678

Output :-

If it returns  "0" , then the  "VALUE_TO_SEARCH" is NOT available in XML_BLOB for the transaction_no

If it returns value greater than "0" , then the "VALUE_TO_SEARCH"  is  Available in XML_BLOB for the transaction_no. Actually, it returns the position of the value in XML_BLOB.  if the return value is "72", it means the value is available from 72nd character of the XML_BLOB.