Rewrite Oracle statement for linked server via SQL Server 2005

  • Hi,

    we want to switch to linked server instead of installing the Oracle client in the IIS. We are using .NET to create the applications.

    I would like to convert the following:

    DECLARE l_num_count NUMBER := 0;

    BEGIN

    SELECT COUNT(*) into l_num_count FROM tablename WHERE fieldname = 'value';

    IF l_num_count = 0 THEN

    INSERT INTO tablename (fieldlist) VALUES (valuelist);

    ELSE

    UPDATE tablename SET list(field = value);

    END IF;

    COMMIT;

    END;

    Any idea welcome - at the moment I am stuck with the transaction and commit on Oracle side. I found the statements for SELECT, INSERT and UPDATE and they are working - every one for itself.

  • I'm really sorry but I do not understand your question.

    Anyway...

    1- Your commit statement is Okay.

    2- Your update statement is updating all rows in the table.

    3- I would use merge statement instead of you insert into/update logic.

    _____________________________________
    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.
  • My problem is to convert this statement from Oracle (it is working perfectly) to SQL linked server.

    The syntax for Insert and Update is clear:

    INSERT OPENQUERY (linkedserver, 'SELECT fieldlist FROM tablename') VALUES (valuelist);

    UPDATE OPENQUERY (linkedserver, 'SELECT * FROM tablename WHERE field = ''value'') SET list(field=value);

    SELECT * from OPENQUERY(linkedserver,'SELECT COUNT(*) FROM tablename WHERE field = ''value''')

    But I am not sure how to get the rest via linked server to Oracle. And: I have to send the select, check the result and then send the INSERT or UPDATE.

Viewing 3 posts - 1 through 2 (of 2 total)

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