title: IF EXISTS UPSERT
body: <p>I currently have a stored procedure that updates files if they exist and inserts if they don't. The problem with the query below is it seem to be inserting all records regardless.</p>
  
  <p>I can't seem to spot what the problem is?</p>
  
  <p>Using SQL server 2012</p>
  
  <pre><code>ALTER PROC [dbo].[usp_UpdateInsertOptimise_new]
  @Resid int = null,
  @PostCode varchar(50),
  @DriverID int,
  @droporder int,
  @X int,
  @Y int,
  @Calculate bit,
  @uidref bigint,
  @Description1 varchar(500),
  @Description2 varchar(500),
  @Description3 varchar(500),
  @Uiddriver int,
  @reservationsid int,
  @Time varchar(50),
  @TimeInPoint int,
  @UniqueString varchar(50),
  @DistanceBetweenTwoPoints float,
  @Weight int,
  @TimeBetweenPoints int,
  @TimeFrom datetime,
  @TimeTo datetime,
  @TotalDistance float,
  @Display varchar(150),
  @Volume float,
  @VehicleID int,
  @Pallets int,
  @AddressId int,
  @CD nvarchar(10),
  @ForkLiftRequired bit,
  @HazardousRequired bit,
  @FridgeRequired bit,
  @TailLiftRequired bit,
  @CurtinsideRequired bit,
  @CDID varchar(50),
  @CDOrder int,
  @OriginalDate datetime,
  @ArivalTime datetime,
  @TimeForStage int,
  @Optimise bit,
  @ForVehicleGroupID int,
  @DepotID int
  
  AS
  
  if EXISTS (SELECT * FROM optimise WITH (READUNCOMMITTED)
  Where ReservationsID = @ResID)
  
  BEGIN
  UPDATE  dbo.Optimise SET 
  PostCode = @PostCode,
  [DriverID] = @DriverID,
  [droporder] = @DropOrder,
  [X] = @X,
  [Y] = @Y,
  [Calculate] = @Calculate,
  [uidref] = @UIDRef,
  [Description1] = @Description1,
  [Description2] = @Description2,
  [Description3] = @Description3,
  [Uiddriver] = @UIDDriver,
  [reservationsid] = @ReservationsID,
  [Time] = @Time,
  [TimeInPoint] = @TimeInPoint,
  [UniqueString] = @UniqueString,
  [DistanceBetweenTwoPoints] = @DistanceBetweenTwoPoints,
  [Weight] = @Weight,
  [TimeBetweenPoints] = @TimeBetweenPoints,
  [TimeFrom] = @TimeFrom,
  [TimeTo] = @TimeTo,
  [TotalDistance] = @TotalDistance,
  [Display] = @Display,
  [Volume] = @Volume,
  [VehicleID] = @VehicleID,
  [Pallets] = @Pallets,
  [AddressID] = @AddressID,
  [CD] = @CD,
  [ForkliftRequired] = @ForkLiftRequired,
  [HazardousRequired] = @HazardousRequired,
  [FridgeRequired] = @FridgeRequired,
  [TailLiftRequired] = @TailLiftRequired,
  [CurtinsideRequired] = @CurtinsideRequired,
  [CDID] = @CDID,
  [CDOrder] = @CDOrder,
  [OriginalDate] = @OriginalDate,
  [ArivalTime] = @ArivalTime,
  [TimeForStage] = @TimeForStage,
  [Optimise] = @Optimise,
  [ForVehicleGroupID] = @ForVehicleGroupID,
  [DepotID] = @DepotID 
  
  WHERE ReservationsID = @ResID
  
  END
  
  ELSE 
  
  IF NOT EXISTs(SELECT * FROM optimise WITH (READUNCOMMITTED)
  Where ReservationsID = @ResID)
  
  BEGIN
  
  
  
  INSERT INTO [Optimise]
  ([PostCode],
  [DriverID],
  [droporder],
  [X],
  [Y],
  [Calculate],
  [uidref],
  [Description1],
  [Description2],
  [Description3],
  [Uiddriver],
  [reservationsid],
  [Time],
  [TimeInPoint],
  [UniqueString],
  [DistanceBetweenTwoPoints],
  [Weight],
  [TimeBetweenPoints],
  [TimeFrom],
  [TimeTo],
  [TotalDistance],
  [Display],
  [Volume],
  [VehicleID],
  [Pallets],
  [AddressId],
  [CD],
  [ForkliftRequired],
  [HazardousRequired],
  [FridgeRequired],
  [TailLiftRequired],
  [CurtinsideRequired],
  [CDID],
  [CDOrder],
  [OriginalDate],
  [ArivalTime],
  [TimeForStage],
  [Optimise],
  [ForVehicleGroupID],
  [DepotID] )
  
  VALUES (
  @PostCode,
  @DriverID,
  @DropOrder,
  @X,
  @Y,
  @Calculate,
  @UIDRef,
  @Description1,
  @Description2,
  @Description3,
  @UIDDriver,
  @ReservationsID,
  @Time,
  @TimeInPoint,
  @UniqueString,
  @DistanceBetweenTwoPoints,
  @Weight,
  @TimeBetweenPoints,
  @TimeFrom,
  @TimeTo,
  @TotalDistance,
  @Display,
  @Volume,
  @VehicleID,
  @Pallets,
  @AddressId,
  @CD,
  @ForkLiftRequired,
  @HazardousRequired,
  @FridgeRequired,
  @TailLiftRequired,
  @CurtinsideRequired,
  @CDID,
  @CDOrder,
  @OriginalDate,
  @ArivalTime,
  @TimeForStage,
  @Optimise,
  @ForVehicleGroupID,
  @DepotID)
  
  
  END
  
  GO
  </code></pre>