Hello,
I'm working on a project that requires me to move data between a MS SQL Server environment and an Oracle Environment. I'm not having any issues going from Oracle to SQL, as the Embedded if statements required to do an intelligent Insert or Update work without any issues.
I am having a really difficult time getting the converse to work, from SQL Server to Oracle. I've tried two methods to do an upsert:
1) Selecting the count into a variable and then basing my if statement of that variable
2) Using a Merge Table like this:
Neither of these two methods have worked, as I get generic "Invalid Query" errors, even though the queries themselves work when run through SQL Developer on the Oracle Servers.
Any insight on what I need to do to make this work would be greatly appreciated.
-Justin
I'm working on a project that requires me to move data between a MS SQL Server environment and an Oracle Environment. I'm not having any issues going from Oracle to SQL, as the Embedded if statements required to do an intelligent Insert or Update work without any issues.
I am having a really difficult time getting the converse to work, from SQL Server to Oracle. I've tried two methods to do an upsert:
1) Selecting the count into a variable and then basing my if statement of that variable
Declare @RowExists INT
SELECT COUNT(ID) into @RowExists where ID = $ID
IF (@RowExists = 0)
THEN
INSERT HERE
ELSE
UPDATE HERE
END IF2) Using a Merge Table like this:
MERGE INTO TABLE t
USING (SELECT $Col1,$Col2,$Col3 FROM DUAL) s
ON t.id = s.Col1
WHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATENeither of these two methods have worked, as I get generic "Invalid Query" errors, even though the queries themselves work when run through SQL Developer on the Oracle Servers.
Any insight on what I need to do to make this work would be greatly appreciated.
-Justin
-
Hi Justin,
According to how I understand your question, you would like to migrate data from MSSQL Server to Oracle. What I do not understand is if you are attempting to copy the data from MSSQL to Oracle using one single SQL statement (as you cannot run MERGE INTO with tables from two different database systems).
However, if you want to do an upsert operation, you might think about another solution. This has been already discussed in the following thread: Try to Update record, Insert if not found. As you can see, you can do upsert very easily in CloverETL using multiple components even when you need to work with multiple tables stored in distinct database systems.
However, if you would like to run these statements in Clover, you should use DBExecute component. This component is basically used for more complex SQL/DML/DDL statements (for more information about this component, refer to http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/dbexecute.html). If you decide for using DBExecute to access MSSQL database, you should keep in mind that the default statement delimiter is semicolon (;). Therefore, if SQL Server expects to receive semicolon as part of your statement, you should set "SQL statement delimiter" attribute to a character that is not used in your statement.
If you have any further question, can you post the whole graph, so that I can better understand what you have been attempting to do? -
slechtaj,
Thanks for the response! The linked post was great, unfortunately I'm being limited to use of the community edition only, which doesn't have access to some of those features. I'm still attempting to convince the powers that be to provide me the full version.
What I'm attempting to do is perform a select statement on a MS SQL Server to grab some data and then upsert it into a remote oracle table. The merge statement I was attempting to use works as I am selecting the data from "Dual" on the remote DB, which is a generic table with one record and just specifying the data I want returned using Clover metadata variables. In reality, what I expected to happen was N number of merge statements would run, merging into the destination table for each record inside the metadata coming from the MS SQL Statement.
I'm doing the SQL statement inside the DBOutputTable, which works perfectly fine when I'm using MS SQL. I'm attaching an example graph that I did that moves data from a MS SQL table to Another and does the upsert operation. -
Justin,
Although our Community Edition offers only limited variety of components, you may do the upsert another way. Probably the easiest solution would be preparing the whole query upfront in Reformat component.
So let's say your MERGE statement is supposed to look like this:MERGE INTO idcard mt
USING (SELECT <value> AS idNumber, <value> AS lastName, <value> AS firstName, <value> AS personType FROM DUAL) dt
ON (mt.id = dt.id)
WHEN MATCHED THEN
UPDATE SET mt.value = dt.value
WHEN NOT MATCHED THEN
INSERT (id_num, lname, fname, status1) VALUES (dt.idNumber, dt.lastName, dt.firstName, dt.personType)
Then you can fill this statement with values (represented by <value> in above code) in reformat and send the statement as whole to DBExecute. The only thing you need to set in DBExecute is to make the component expect a query from input port (setting the Querry URL tribute). I tried to put the changes into your graph (see attached file), but I could test it with your data, so make sure all the names are correct. -
In a .csv file I have a field "DATE_CREATE" containing a date "17/02/16".
For this field, I set in the metadata, type: "date" and format: "dd/MM/yy" like this:<Field format="dd/MM/yy" name="DATE_CREATE" type="date"/>
I don't understand how cloverETL can fail with this error:Illegal char <:> at index <xxx> MERGE INTO (...) 'Wed Feb 17 00:00:00 SBT 2016' (...)
and display in the log file a date with this wrong format: Wed Feb 17 00:00:00 SBT 2016
When I debug between ParallelReader and Reformat and view data, the format of date is good "17/02/16" but in Transform editor $in.0.DATE_CREATE equals 'Wed Feb 17 00:00:00 SBT 2016' and ignore the format of metadata.
How to resolve this problem?
Thanks
Please sign in to leave a comment.
Comments 5