Update or Insert Row depending on whether row is present in Microsoft SQL Server 2005
- by Srikanth
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
