Rewrite Oracle statement for linked server via SQL Server 2005

• birgit.schelloeh

  Old Hand

  Points: 343

  More actions

  September 29, 2008 at 6:42 am

  #193757

  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.

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  September 29, 2008 at 7:08 am

  #878374

  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.

• birgit.schelloeh

  Old Hand

  Points: 343

  More actions

  September 29, 2008 at 7:19 am

  #878380

  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.