T-SQL Update else Insert stored procedure with Rank() Partition function (SQL 2008)

I have a stored procedure that partitions by date and then ranks one field for each date. This has been tested and works correctly. The current stored procedure DELETEs the rows if they exists, then just does an INSERT. I would like to turn this into a stored procedure that UPDATES if the row exists else INSERTS a new value. I have looked at many of the posting regarding Update else Insert but have not been able to get the correct update syntax.

the table definition is

   (@SeriesID   smallint,
    @SymbolID   smallint,
    @Date       smalldatetime,
    @Val        real)

The stored procedure I wish to turn into a UPDATE else INSERT is:

CREATE PROCEDURE [dbo].[RankPerDate] 
        @StartDate  smallDateTime,
        @EndDate    smallDateTime,
        @SeriesToRankID smallint,
        @RankedSerieID  smallint
AS
    -- remove time series if it exists
    BEGIN
     DELETE FROM SeriesFloat
     WHERE SeriesID = @RankedSerieID AND (Date >= @StartDate) AND (Date <= @EndDate)    
    END

    BEGIN 
        INSERT INTO SeriesFloat
        SELECT SeriesID = @RankedSerieID, SymbolID, Date, RANK() OVER (PARTITION BY Date ORDER BY Val DESC) AS Val
        FROM SeriesFloat 
        WHERE (SeriesID = @SeriesToRankID)  AND (Date >= @StartDate) AND (Date <= @EndDate)
    END

The Stored procedure sample inputs:

 -- sample values for testing & parameters for stored procedure     
@StartDate = '1999-01-08 00:00:00';
@EndDate   = '1999-01-09 00:00:00';
@SeriesToRankID = 12; -- id of the series that is to be ranked
@RankedSerieID  = 35; -- id of the series that is to be updated/inserted

Sample Table data prior to query:

SeriesID    SymbolID  Date                  Val
12          2011      1999-01-08 00:00:00   4215000
12          2012      1999-01-08 00:00:00   3215580
12          2013      1999-01-08 00:00:00   2029895
12          2011      1999-01-09 00:00:00   2029895
12          2012      1999-01-09 00:00:00   3395788
12          2013      1999-01-09 00:00:00   4029895
35          2012      1999-01-09 00:00:00   4 -- this row will be updated
35          2013      1999-01-09 00:00:00   8 -- this row will be updated

Results of ranking:

SeriesID    SymbolID  Date                  Val
35          2011      1999-01-08 00:00:00   1 -- this row is inserted
35          2012      1999-01-08 00:00:00   2 -- this row is inserted
35          2013      1999-01-08 00:00:00   3 -- this row is inserted
35          2011      1999-01-09 00:00:00   3 -- this row is inserted
35          2012      1999-01-09 00:00:00   2 -- this row is updated
35          2013      1999-01-09 00:00:00   1 -- this row is updated

Sample Table data after stored procedure in run:

SeriesID    SymbolID  Date                  Val
12          2011      1999-01-08 00:00:00   4215000
12          2012      1999-01-08 00:00:00   3215580
12          2013      1999-01-08 00:00:00   2029895
12          2011      1999-01-09 00:00:00   4029895
12          2012      1999-01-09 00:00:00   3395788
12          2013      1999-01-09 00:00:00   2029895
35          2011      1999-01-08 00:00:00   1 -- this row was inserted
35          2012      1999-01-08 00:00:00   2 -- this row was inserted
35          2013      1999-01-08 00:00:00   3 -- this row was inser开发者_StackOverflow中文版ted
35          2011      1999-01-09 00:00:00   3 -- this row was inserted
35          2012      1999-01-09 00:00:00   2 -- this row was updated
35          2013      1999-01-09 00:00:00   1 -- this row was updated

Can anyone provide an example of how this is done?

After all the teeth pulling, here is what I came up with. Your ORDER BY Val within the RANK() OVER() clause didn't make sense (since the Val is just the ranking and gets re-assigned). Based on your sample output I guessed that this ranking should be determined by SymbolID.

USE tempdb;
GO

IF OBJECT_ID('dbo.SeriesFloat') IS NOT NULL
    DROP TABLE dbo.SeriesFloat;
GO

-- suggest using DATE since you don't care about time
-- also does the Val column really need to be REAL?
-- could probably be an INT.

CREATE TABLE dbo.SeriesFloat
(
    SeriesID   SMALLINT,
    SymbolID   SMALLINT,
    [Date]     SMALLDATETIME,
    Val        REAL
);

INSERT dbo.SeriesFloat SELECT 12, 2011, '1999-01-08', 4215000;
INSERT dbo.SeriesFloat SELECT 12, 2012, '1999-01-08', 3215580;
INSERT dbo.SeriesFloat SELECT 12, 2013, '1999-01-08', 2029895;
INSERT dbo.SeriesFloat SELECT 12, 2011, '1999-01-09', 4029895;
INSERT dbo.SeriesFloat SELECT 12, 2012, '1999-01-09', 3395788;
INSERT dbo.SeriesFloat SELECT 12, 2013, '1999-01-09', 2029895;
INSERT dbo.SeriesFloat SELECT 35, 2012, '1999-01-09', 4;
INSERT dbo.SeriesFloat SELECT 35, 2013, '1999-01-09', 8;

-- change these two params to test larger ranges (up to 2,048 days):

DECLARE @Start DATE = '1999-01-08',
        @End   DATE = '1999-01-09',
        @SeriesToRankID SMALLINT = 12,
        @RankedSerieID  SMALLINT = 35;

-- let's figure out the set of days - good for a range up to 2,048 days
-- if you need more than that, build a table of numbers

DECLARE @DaysInRange TABLE
(
    d DATE
);

INSERT @DaysInRange
    SELECT DISTINCT DATEADD(DAY, number, @Start)
        FROM [master].dbo.spt_values
        WHERE number BETWEEN 0 AND DATEDIFF(DAY, @Start, @End);

-- let's insert the rows that don't yet exist

INSERT dbo.SeriesFloat(SeriesID, SymbolID, [Date])
SELECT DISTINCT SeriesID = @RankedSerieID, s.SymbolID, d.d
    FROM dbo.SeriesFloat AS s 
    CROSS JOIN @DaysInRange AS d
    WHERE s.SeriesID = @SeriesToRankID
    AND NOT EXISTS
    (
        SELECT 1 FROM dbo.SeriesFloat 
            WHERE SeriesID = @RankedSerieID
            AND [Date] = d.d
            AND SymbolID = s.SymbolID
    );

-- then update all of them with ranking

WITH s AS 
(
    SELECT 
        SeriesID, SymbolID, [Date],
        Val = ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY SymbolID)
    FROM 
        dbo.SeriesFloat
    WHERE 
        SeriesID = @RankedSerieID
)
UPDATE sf SET Val = s.Val
    FROM dbo.SeriesFloat AS sf
    INNER JOIN s 
        ON sf.SymbolID = s.SymbolID
        AND sf.[Date] = s.[Date]
    WHERE sf.SeriesID = @RankedSerieID;

SELECT SeriesID, SymbolID, [Date], Val
    FROM dbo.SeriesFloat 
    ORDER BY SeriesID, [Date], Val;
GO

I was not interested in trying MERGE to solve this problem, but you can check out the docs here:

http://msdn.microsoft.com/en-us/library/bb510625(SQL.100).aspx

As an aside, why do you need to store the Val ranking? Seems like you will always be able to generate this at query time (using a view if you use that column a lot).