WHERE clause with INSERT statement, "Incorrect Syntax" at the WHERE, and IF NOT EXISTS running regardless of if record exists

2
votes

I have this INSERT query:

INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate) 
VALUES (1, 11, 1, DATEADD(day, 1, GETDATE()))
WHERE NOT EXISTS 
    (SELECT * FROM Appointments 
     WHERE StaffID = 1 AND CustomerID = 11 AND TimeSlot = 1 AND AppDate = DATEADD(day, 1, GETDATE()));

Yet it says "Incorrect syntax near the keyword 'WHERE'"

This is very basic error, but have no clue whats up.

I have also tried using IF NOT EXISTS, which gives no syntax errors, but also doesn't work, preforming the insert even though the record with the values does already exist:

IF NOT EXISTS 
    (
        SELECT * FROM Appointments 
        WHERE StaffID = 1 AND CustomerID = 11 AND TimeSlot = 1 AND AppDate = DATEADD(day, 1, GETDATE())
    )
    BEGIN
        INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate) 
        VALUES (1, 11, 1, DATEADD(day, 1, GETDATE()))
END;

The record with the values 1, 11, 1, and tomorrow does 100% already exist, yet inserts it again.

2
sqlsql-server-2008wheresql-insert

2 Answers

3
votes

You can't specify any WHERE clause with the INSERT .. VALUES statement. But you can do that with the INSERT .. SELECT statement as shown below.

Also...

The record with the values 1, 11, 1, and tomorrow does 100% already exist, yet inserts it again.

GETDATE() returns a timestamp with fractional seconds precision, which is unlikely to already exist in your table. You probably want to use the DATE data type, e.g. using 

DATEADD(day, 1, CAST(GETDATE() AS DATE))

INSERT .. SELECT

INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate) 
SELECT 1, 11, 1, DATEADD(day, 1, CAST(GETDATE() AS DATE))
WHERE NOT EXISTS (
  SELECT * 
  FROM Appointments 
  WHERE StaffID = 1 
    AND CustomerID = 11 
    AND TimeSlot = 1 
    AND AppDate = DATEADD(day, 1, CAST(GETDATE() AS DATE))
);

INSERT .. SELECT with common table expression to avoid duplication

Or perhaps, avoiding calculating GETDATE() several times:

WITH ins (StaffID, CustomerID, TimeSlot, AppDate)
  AS (SELECT 1, 11, 1, DATEADD(day, 1, CAST(GETDATE() AS DATE)))
INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate) 
SELECT ins.StaffID, ins.CustomerID, ins.TimeSlot, ins.AppDate
FROM ins
WHERE NOT EXISTS (
  SELECT * 
  FROM Appointments 
  WHERE StaffID = ins.StaffID 
    AND CustomerID = ins.CustomerID
    AND TimeSlot = ins.TimeSlot 
    AND AppDate = ins.AppDate
);

INSERT .. SELECT with set operations

Or, again, more concisely using set operations:

INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate)
SELECT 1, 11, 1, DATEADD(day, 1, CAST(GETDATE() AS DATE))
EXCEPT
SELECT StaffID, CustomerID, TimeSlot, AppDate
FROM Appointments

MERGE

Actually, the whole "insert if not exists" concept can be modelled using MERGE as well:

MERGE INTO Appointments a
USING (
  SELECT 
    1 StaffID, 
    11 CustomerID,
    1 TimeSlot,
    DATEADD(day, 1, CAST(GETDATE() AS DATE)) AppDate
) ins
ON (
  a.StaffID = ins.StaffID AND 
  a.CustomerID = ins.CustomerID AND 
  a.TimeSlot = ins.TimeSlot AND 
  a.AppDate = ins.AppDate
)
WHEN NOT MATCHED THEN INSERT (StaffID, CustomerID, TimeSlot, AppDate)
  VALUES (ins.StaffID, ins.CustomerID, ins.TimeSlot, ins.AppDate)
1
votes

you shouldn't involve time while insertion and write a query as:

INSERT INTO Appointments (StaffID, CustomerID, TimeSlot, AppDate) 
select 1, 11, 1, DATEADD(day, 1, cast(GETDATE() as date))
WHERE NOT EXISTS 
(SELECT * 
 FROM Appointments 
 WHERE StaffID = 1 AND CustomerID = 11 AND TimeSlot = 1 
 AND AppDate = DATEADD(day, 1, cast(GETDATE() as date))
 )