Problems to transcript a Oracle SQL Script

Question

Post reply

Problems to transcript a Oracle SQL Script

franz.ladda

SSC Rookie

Points: 35
More actions
September 3, 2009 at 1:59 am

#214710

Hello together, i have problems to build a T-SQL script for MS SQL 2005, which should do the same like the following Oracle script:
MERGE INTO unknown_ip ip1
USING (SELECT ip_address, volume_ib, volume_ob, device_id, remark
FROM ip_temp) ip
ON (ip1.ip_address = ip.ip_address
AND ip1.device_id = ip.device_id
AND TRUNC(ip1.log_date) = TRUNC(SYSDATE))
WHEN MATCHED THEN
UPDATE SET ip1.volume_ib = ip1.volume_ib + ip.volume_ib,
ip1.volume_ob = ip1.volume_ob + ip.volume_ob
WHEN NOT MATCHED THEN
INSERT (ip1.log_date, ip1.ip_address, ip1.volume_ib, ip1.volume_ob
, ip1.device_id, ip1.remark)
VALUES (TRUNC(SYSDATE), ip.ip_address, ip.volume_ib, ip.volume_ob
, IP.device_id, ip.remark);
The function TRUNC i have successfully build, the value SYSDATE i have replaced with GETDATE(), but i have problems to map the MERGE and CASE functions.
Please help, Best Regards
Franz Ladda

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

MERGE is a new feature of SQL Server 2008, there's no such feature in SQL Server 2005.

I don't see any CASE in your query: am I missing something?

-- Gianluca Sartori

franz.ladda SSC Rookie Points: 35 More actions · Answer 2

Hello,

i mean the WHEN MATCHED an WHEN NOT MATCHED STATEMENT s, it is logical like the CASE funktion.

Best regards

Franz Ladda

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Thanks for clarifying.

I'm afraid you will have to do it the old way:

1) update ... from src inner join dest

2) insert into .... where not exists....

-- Gianluca Sartori

franz.ladda SSC Rookie Points: 35 More actions · Answer 4

Sorry,

but i have no other possibility to doe it the old way

Best Regards

Franz Ladda

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

As a rule of thumbs there is no way of reusing PL/SQL code as Transact-SQL - provided code does something more complex than a "select column from table" or similar statement.

The "translation" process usually involves reading and understanding what the original Oracle code does then, write it from scratch on SQL Server side.

The same applies when going from SQL Server to Oracle - lots of fun! 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

franz.ladda (9/3/2009)
Hello together, i have problems to build a T-SQL script for MS SQL 2005, which should do the same like the following Oracle script:
MERGE INTO unknown_ip ip1
USING (SELECT ip_address, volume_ib, volume_ob, device_id, remark
FROM ip_temp) ip
ON (ip1.ip_address = ip.ip_address
AND ip1.device_id = ip.device_id
AND TRUNC(ip1.log_date) = TRUNC(SYSDATE))
WHEN MATCHED THEN
UPDATE SET ip1.volume_ib = ip1.volume_ib + ip.volume_ib,
ip1.volume_ob = ip1.volume_ob + ip.volume_ob
WHEN NOT MATCHED THEN
INSERT (ip1.log_date, ip1.ip_address, ip1.volume_ib, ip1.volume_ob
, ip1.device_id, ip1.remark)
VALUES (TRUNC(SYSDATE), ip.ip_address, ip.volume_ib, ip.volume_ob
, IP.device_id, ip.remark);
The function TRUNC i have successfully build, the value SYSDATE i have replaced with GETDATE(), but i have problems to map the MERGE and CASE functions.
Please help, Best Regards
Franz Ladda

SQL Server 2005 doesn't have a MERGE statement. You will need to write two separate queries. For the WHEN MATCHED portion, you will need to write an UPDATE. For the WHEN NOT Matched portion, you will need to write an insert.

Also, putting a function on a datetime column isn't a good idea for performance... not even in Oracle.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 7

I must remember this thread next time someone tries to sell SQL code portability to me :laugh:

I can't help that mention that putting a function on a datetime column is fine for performance - so long the calculation also exists as a computed column, which is persisted or appropriately indexed. The SQL Server optimizer is very clever like that. Oracle provides indexes on functions directly I think...?

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

Paul White (9/9/2009)Oracle provides indexes on functions directly I think...?

You are correct once again. Oracle do supports "function based indexes".

Last but not least, in the Oracle world applying a function to a predicate column indexed by a normal index would prevent Oracle optimizer to take advantage of such an index.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150468 More actions · Answer 9

PaulB (9/9/2009)You are correct once again.

It can't last 😀

PaulB (9/9/2009)Last but not least, in the Oracle world applying a function to a predicate column indexed by a normal index would prevent Oracle optimizer to take advantage of such an index.

Same in SQL Server. I just like surprising people with the 'indexed function' trick in SQL Server...

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

amir.mochtar Old Hand Points: 323 More actions · Answer 10

amir.mochtar

Old Hand

Points: 323

August 16, 2011 at 4:22 am

#1369000

- deleted -

LutzM SSC Guru Points: 107049 More actions · Answer 11

Please don't hijack a thread.

The issue you describe is not even related to the subject discussed in the original thread.

Ask your question using a new thread in the SSIS section.

Or you might even want to perform an internet search before. The question you asked may already be answered somewhere. 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

amir.mochtar Old Hand Points: 323 More actions · Answer 12

LutzM (8/16/2011)
Please don't hijack a thread.
The issue you describe is not even related to the subject discussed in the original thread.
Ask your question using a new thread in the SSIS section.
Or you might even want to perform an internet search before. The question you asked may already be answered somewhere. 😉

ok. sorry then ...