| Sql server to Oracle Migration [message #601138] |
Sun, 17 November 2013 02:22  |
 |
Ora.explorer
Messages: 3
Registered: August 2012
Location: Los Angeles
|
Junior Member |
|
|
Hi Everyone,
I am trying to migrate a procedure from sql server to Oracle. I am using sql developer translator scratch editor. I am having problem converting the merge statement. I need you help on this.
For records with updates, this will set Active flag bit = 0 and insert the new data
INSERT INTO landing(x,y,z,[a],ActiveFlagBit) -- INSERTS updated records
SELECT x,y,z,[a],ActiveFlagBit
FROM(
MERGE staging S
USING landing L
ON L.[y] = S.[y]
AND ((L.[a] = S.[a]) OR (L.[b] IS NULL AND S.[b] IS NULL) )
WHEN MATCHED
AND ActiveFlagBit = 1 -- Testing for column differences (Updated entries)
THEN UPDATE SET S.ActiveFlagBit = 0 -- SETS existing (old) entries to ActiveFlagBit Zero
WHEN NOT MATCHED BY TARGET -- INSERT new records
THEN INSERT ()
OUTPUT $action, '1' AS ActiveFlagBit, ......
) AS ActiveInserts(Action,ActiveFlagBit ......])
WHERE Action = 'UPDATE';
The above is a skeleton of sql server merge. I am able to do the inner merge and i dont know how to replace the output statement and use it for insertand also what can i use in oracle for action?
Can i create a cursor and store the data and use that in insert statement? what should i replace action with. Experts, i am new to this migration, Your suggestions are highly appreciated.
|
|
|
|
|
|
| Re: Sql server to Oracle Migration [message #601140 is a reply to message #601139] |
Sun, 17 November 2013 03:01  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've had another look at your code. I know nothing of SQL Server, but it looks to me as though you need something like this:
insert into landing(x,y,z)
select x,y,z from s join l using(y); |
|
|
|
|
|
| Re: Sql server to Oracle Migration [message #601159 is a reply to message #601157] |
Sun, 17 November 2013 14:29 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sorry, man, I do not understand you. But it sounds as though you are making something very simple into something very complicated. Forget all this "output" and "action" stuff, you don't need that in Oracle. If you want to read one row source, and either insert or update another row source, just use MERGE. I've given you the link to the relevant doc.
SQL Server has all sorts of tricks for covering up problems with read consistency. Oracle doesn't have these problems. Just use pure SQL, and you will be OK.
|
|
|
|
| Re: Sql server to Oracle Migration [message #601164 is a reply to message #601159] |
Sun, 17 November 2013 22:59 |
|
Ora.explorer
Messages: 3
Registered: August 2012
Location: Los Angeles
|
Junior Member |
|
|
INSERT INTO landing(c1,c2...) -- INSERTS updated records
SELECT c1,c2...
FROM(
MERGE staging S
USING landing L
ON L.c1 = S.c2
WHEN MATCHED
AND ActiveFlagBit = 1
THEN UPDATE SET S.ActiveFlagBit = 0 -- SETS existing (old) entries to ActiveFlagBit Zero
WHEN NOT MATCHED BY TARGET -- INSERT new records
THEN INSERT ()
VALUES ()
OUTPUT $action, '1' AS ActiveFlagBit,l.c3,l.c4.....) AS ActiveInserts(Action,ActiveFlagBit)
WHERE Action = 'UPDATE';
This is the sample code. I need to convert it into oracle. Merge is simple so i am able to do that. I don't know how to use this output and action.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
