Problems to transcript a Oracle SQL Script

  • 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

  • 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

  • Hello,

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

    Best regards

    Franz Ladda

  • 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

  • Sorry,

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

    Best Regards

    Franz Ladda

  • 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.
  • 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)

  • 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 (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.
  • 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...

  • - deleted -

  • 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]

  • 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 ...

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

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