Using Upsert SQL query

use NitronTradingBeta
go


drop table price

Create Table dbo.Price
(
PriceId int not null IDENTITY constraint Price_PK PRIMARY KEY,
AsofDate DateTime not null ,
Workbook varchar(255) null,
Worksheet varchar(255) null,
CellAddress varchar(32) null,
ADate DateTime null ,
AValue decimal(30,10) null,
UpdatedOn datetime,
CreatedOn datetime not null,
UpdatedID varchar(32) null,
CreatedByID varchar(32) null,
)

create nonclustered index PRICE_IDX_asOFdATE_aVALUE ON PRICE(AsofDate,Workbook,Worksheet,CellAddress,AValue)
go

create nonclustered index PRICE_IDX_asOFdATE_ADate ON PRICE(AsofDate,Workbook,Worksheet,CellAddress,ADate)
go

select getdate()
select CAST (getdate() as DATE)

select * from price
exec dbo.usp_PriceAddUpdate_AValue 'wbtest','sheet1','A1','2016-01-10',1.00079

drop proc dbo.usp_PriceAddUpdate_AValue

create proc dbo.usp_PriceAddUpdate_AValue
@Workbook varchar(255),
@Worksheet varchar(255),
@CellAddress varchar(255),
@AsofDate Datetime,
@aValue NUMERIC
AS

SET NOCOUNT ON

  DECLARE @rowcount INT;     -- store the number of rows that get inserted

INSERT INTO dbo.Price
 (
WorkBook,
Worksheet,
CellAddress,
AsofDate,
AValue
 )
  SELECT TOP 1                 -- important since we're not constraining any records
Workbook=@Workbook,
Worksheet=@Worksheet,
CellAddress=@CellAddress,
    AsofDate = @AsofDate,
Value=@AValue
 
  FROM Price
  WHERE NOT EXISTS             -- do not want to duplicate
  (
    SELECT 1
    FROM Price
    WHERE
Workbook=@Workbook AND
Worksheet=@Worksheet AND
CellAddress=@CellAddress AND
AsofDate = @AsofDate
  )



  SET @rowcount = @@ROWCOUNT     -- return back the rows that got inserted
   print 'rows affected from insert '+ cast (@rowcount as varchar)

  -- if no rows were inserted, the row must exist, so update
  UPDATE PRICE
  SET AValue = @AValue
  WHERE @rowcount = 0 AND
Workbook=@Workbook AND
Worksheet=@Worksheet AND
CellAddress=@CellAddress AND
AsofDate = @AsofDate