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

Question

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

Answer 1

What you are trying to achieve is called an UPSERT. SQL2008 supports this directly through MERGE

For SQL2005 you will need to break it into two operations. One UPDATE using an INNER JOIN on key and one INSERT using an OUTER JOIN for the extra records.

SET NOCOUNT ON

IF (OBJECT_ID('tempdb..#TempRanges') IS NULL)
BEGIN
CREATE TABLE #TempRanges(
    [iRangeID] [int] NOT NULL,
    [iSetID] [int] NOT NULL,
    [fRangeFrom] [float] NOT NULL,
    [fRangeTo] [float] NOT NULL,
    [bTakeoutEnabled] [bit] NOT NULL,
 CONSTRAINT [PK_TempRanges] PRIMARY KEY CLUSTERED 
(
    [iRangeID] ASC,
    [iSetID] ASC
)
)
END
ELSE
BEGIN
DELETE FROM #TempRanges
INSERT INTO #TempRanges([iRangeID], [iSetID], [fRangeFrom], [fRangeTo], [bTakeoutEnabled])
SELECT '1', '6', '0', '20', '0' UNION ALL
SELECT '4', '6', '21', '40', '0' UNION ALL
SELECT '5', '6', '999', '60', '0' UNION ALL
SELECT '6', '6', '61', '80', '0' 
END



DECLARE @doc XML

SET @doc = '<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>'

DECLARE @idoc INT

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc


UPDATE    #TempRanges
SET    fRangeFrom =ParameterRangeFrom, fRangeTo =ParameterRangeTo, bTakeoutEnabled =ParameterAutoTakeOut
FROM OPENXML(@idoc, '/Strategy/ParameterRange', 1)
WITH (ParameterSetID int,ParameterRangeID int,ParameterRangeFrom float,ParameterRangeTo float,ParameterAutoTakeOut bit)  AS input
INNER JOIN #TempRanges ON #TempRanges.iRangeID = input.ParameterRangeID AND #TempRanges.iSetID = input.ParameterSetID

PRINT CONVERT(VARCHAR(10) ,@@ROWCOUNT) + ' Updated'


INSERT INTO #TempRanges
           ([iRangeID]
           ,[iSetID]
           ,[fRangeFrom]
           ,[fRangeTo]
           ,[bTakeoutEnabled])
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)  AS input
LEFT OUTER JOIN #TempRanges ON #TempRanges.iRangeID = input.ParameterRangeID AND #TempRanges.iSetID = input.ParameterSetID
WHERE #TempRanges.iRangeID IS NULL


PRINT CONVERT(VARCHAR(10) ,@@ROWCOUNT) + ' Inserted'

SELECT * FROM #TempRanges