Update or Insert Row depending on whether row is present in Microsoft SQL Server 2005

Posted by Srikanth on Stack Overflow See other posts from Stack Overflow or by Srikanth
Published on 2010-05-23T16:21:22Z Indexed on 2010/05/23 16:40 UTC
Read the original article Hit count: 239

Filed under:
|

Hi,

I am passing a XML document as a input to a stored procedure in Microsoft SQL Server 2005. This is the sample XML being passed as input

<Strategy StrategyID="0" TOStrategyID="8" ShutdownQtySell="1" ShutdownQtyBuy="1">
      <ParameterRange ParameterSetID="6" ParameterRangeID="1" ParameterRangeFrom="0" ParameterRangeTo="20" ParameterAutoTakeOut="False">
      </ParameterRange>
      <ParameterRange ParameterSetID="6" ParameterRangeID="4" ParameterRangeFrom="21" ParameterRangeTo="40" ParameterAutoTakeOut="False">
      </ParameterRange>
      <ParameterRange ParameterSetID="6" ParameterRangeID="5" ParameterRangeFrom="41" ParameterRangeTo="60" ParameterAutoTakeOut="False">
      </ParameterRange>
      <ParameterRange ParameterSetID="6" ParameterRangeID="6" ParameterRangeFrom="61" ParameterRangeTo="80" ParameterAutoTakeOut="False">
      </ParameterRange>
      <ParameterRange ParameterSetID="6" ParameterRangeID="7" ParameterRangeFrom="81" ParameterRangeTo="100" ParameterAutoTakeOut="False">
      </ParameterRange>
</Strategy>

I am able to retrieve the data using OpenXML functionality in SQL server

I am using this to get the data corresponding to ParameterRange rows

SELECT ParameterRangeID as iRangeID,  
       ParameterSetID as iSetID,  
       ParameterRangeFrom as fRangeFrom,  
       ParameterRangeTo as fRangeTo,  
       ParameterAutoTakeOut as bTakeoutEnabled  
 FROM OPENXML(@idoc, '/Strategy/ParameterRange', 1)  
 WITH (ParameterSetID int,ParameterRangeID int,ParameterRangeFrom float,ParameterRangeTo float,ParameterAutoTakeOut bit)  

Now, I need to insert/update these rows into a table TempRanges which has (iRangeID,iSetID) as the primary key.
If there is a row with the primary key, I want to update it the latest values and If there is no row with that primary key, I need to insert into the table.

How can I accomplish this inside the Stored Procedure ?

Thanks, Sri

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2005