Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle merge statement (Read 7647 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle merge statement
15.03.06 at 08:49:12
 
SQL*XL user Fred asked the following question.
 
Question:
Does SQL*XL support the Oracle merge command for "update or insert"? It would look at the primary key columns, updating matched records (if changed) and inserting new records into the table.
 
Answer:
There is no menu item or feature in SQL*XL that produces this out of the box but... you can make it yourself. See below. If you can make statements in an Excel cell you can select all of them and give them to SQL*XL. Select all the cells that contain the SQL. Open the SQL dialog and choose to get the SQL from the worksheet. Specify that each cell contains a separate statement. Your sheet will look something like this:

begin merge ....; end;
begin merge ....; end;
...

 
This is the merge statement to update the emp table with a row of data. The row of data is coded in the using clause. Note that I have selected the values from the dummy table dual. I had to give each column a name so I can use it easier in the statement lateron.
 
merge into emp mytable
using  
  ( select 123 a
  , 'myname'   b
  , 'myjob'    c
  , null       d
  , sysdate    e
  , 1000       f
  , null       g
  , 10         h  
    from dual
  ) mydata
on (mytable.empno=mydata.a)
when matched then
  update  
  set mytable.ename = mydata.b
  ,   mytable.job = mydata.c
  ,   mytable.mgr = mydata.d
  ,   mytable.hiredate = mydata.e
  ,   mytable.sal = mydata.f
  ,   mytable.comm = mydata.g
  ,   mytable.deptno = mydata.h
when not matched then
  insert  
  ( mytable.empno
  , mytable.ename
  , mytable.job
  , mytable.mgr
  , mytable.hiredate
  , mytable.sal
  , mytable.comm
  , mytable.deptno
  )
  values
  ( mydata.a
  , mydata.b
  , mydata.c
  , mydata.d
  , mydata.e
  , mydata.f
  , mydata.g
  , mydata.h
  )

 
Now you need to build this into a formula in Excel. SQL*XL cannot run merge statements unless they are wrapped in a PL/SQL anonymous block. That is easy to achieve:
begin merge....; end;
 
You do need to cater for nulls on the nullable columns. Use the following Excel syntax for this:
if( isblank(A2), "null",A2 )
This will print the string null if A2 is empty and it will use the value in A2 if it is not blank.
 
I have attached an example file where I have done this for the emp table. The formula is also copied below. It got quite large as you can see.  
 

=CONCATENATE(CONCATENATE("begin merge into emp mytable using (select ",A15," ",$A$1,", '",B15,"' ",$B$1,", '",C15,"' ",$C$1,", ",IF(ISBLANK(D15),"null",D15)," ",$D$1,", to_date( '",TEXT(E15,"dd/mm/yyyy"),"' , 'dd/mm/yyyy' ) ",$E$1,", ",F15," ",$F$1,", ",IF(ISBLANK(G15),"null",G15)," ",$G$1,", ",H15),CONCATENATE(" ",$H$1," from dual ) mydata on (mytable.",,$A$1,"=mydata.",$A$1,") when matched then update set mytable.",$B$1,"=mydata.",$B$1,", mytable.",$C$1,"=mydata.",$C$1,", mytable.",$D$1,"=mydata.",$D$1,", mytable.",$E$1,"=mydata.",$E$1,", mytable.",$F$1,"=mydata.",$F$1,", mytable.",$G$1,"=mydata."),CONCATENATE($G$1,", mytable.",$H$1,"=mydata.",$H$1," when not matched then insert (mytable.",$A$1,", mytable.",$B$1,", mytable.",$C$1,", mytable.",$D$1,", mytable.",$E$1,", mytable.",$F$1,", mytable.",$G$1,", mytable.",$H$1," ) values ( mydata.",$A$1,", mydata.",$B$1,", mydata.",$C$1,", mydata.",$D$1,", mydata."),CONCATENATE($E$1,", mydata.",$F$1,", mydata.",$G$1,", mydata.",$H$1," ); end;"))
Back to top
« Last Edit: 15.03.06 at 09:37:30 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Support for Oracle merge statements
Reply #1 - 04.08.10 at 13:44:55
 
Support for Oracle merge statements
 
Support for Oracle merge statements has been built into SQL*XL. It will be made available in SQL*XL version 4.3.26.
 
Hitherto the ORA-00911: Invalid character was received when executing merge commands. SQL*XL mistook the merge command for a PL/SQL statement and added a semi colon at the end. A workaround is to wrap the merge command in an anonymous PL/SQL block: begin merge ....; end;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1