Gerrit-Jan Linker
|
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;"))
|