Validate if value exists before insert row

Question

I have a query where I send a TableType who have columns EmpKey and TaskId like:

  @AssignNotificationTableType [dbo].[udf_TaskNotification] READONLY

INSERT INTO [TaskNotification] ([TaskId], [EmpKey])
    SELECT
        [ANT].[TaskId], [E].[EmpKey]
    FROM 
        @AssignNotificationTableType AS [ANT]
    INNER JOIN 
        [Employee] AS [E] ON [ANT].[EmpGuid] = [E].[EmpGuid]

So my table looks like this:

+--------------------------------------+--------------------------------------+--------+
|          TaskNotificationId          |                TaskId                | EmpKey |
+--------------------------------------+--------------------------------------+--------+

| EEE3D3F8-F190-E811-841F-C81F66DACA6A | D0440DEB-404C-4006-870F-E95BFFA840E0 |     44 |
| EFE3D3F8-F190-E811-841F-C81F66DACA6A | D0440DEB-404C-4006-870F-E95BFFA840E0 |     49 |
+--------------------------------------+--------------------------------------+--------+

As you can see two items have same TaskId but different Empkey, so suppose if I send again same TaskId D0440DEB-404C-4006-870F-E95BFFA840E0 I want to insert only row only if EmpKey does not exist in that TaskId

So if I send something like:

+--+--------------------------------------+--------+
|  |                TaskId                | EmpKey |
+--+--------------------------------------+--------+
|  | D0440DEB-404C-4006-870F-E95BFFA840E0 |     44 |
|  | D0440DEB-404C-4006-870F-E95BFFA840E0 |     49 |
|  | D0440DEB-404C-4006-870F-E95BFFA840E0 |     54 |
+--+--------------------------------------+--------+

It will only insert last row, because EmpKey 54 does not exist in that TaskId

I try to do in WHERE clause with NOT IN as:

INSERT INTO [TaskNotification] ([TaskId], [EmpKey])
    SELECT
        [ANT].[TaskId], [E].[EmpKey]
    FROM 
        @AssignNotificationTableType AS [ANT]
    INNER JOIN 
        [Employee] AS [E] ON [ANT].[EmpGuid] = [E].[EmpGuid]
    WHERE 
        [E].[EmpKey] NOT IN (SELECT EmpKey
                             FROM [TaskNotification]
                             WHERE TaskId = (SELECT TaskId 
                                             FROM @AssignNotificationTableType))

But when I run it, it just don't insert anything. What am I doing wrong? Regards

Why are you using "WHERE TaskId = (SELECT TaskId FROM @AssignNotificationTableType)" ? — Alvaro Parra, Sep 28 '18 at 18:12
If I don't use it, it get all Employees of employee table or I'm wrong? If I remove it it's just same result, it does not insert anything @AlvaroParra — Pepe, Sep 28 '18 at 18:18
Your last subquery is going to fail if you send more than a single row in your table variable. You should use a NOT EXISTS instead of NOT IN — Sean Lange, Sep 28 '18 at 18:21

Zohar Peled · Accepted Answer · 2018-09-28T21:23:35.750

4

Add the target table as a left join to the select statement:

INSERT INTO [TaskNotification]
(
     [TaskId]
    , [EmpKey]
)
 SELECT
       [ANT].[TaskId]
     , [E].[EmpKey]
       FROM @AssignNotificationTableType AS [ANT]
           INNER JOIN [Employee] AS [E] ON [ANT].[EmpGuid] = [E].[EmpGuid]
           LEFT JOIN [TaskNotification] AS [TN] ON [TN].[TaskId] = [ANT].[TaskId]
               AND [TN].[EmpKey] = [E].[EmpKey]
       WHERE [TN].[PK] IS NULL -- PK stands for the primary key column 
       -- (or first column in of a multiple columns pk)

Please note, however, this in a multithreaded environment such query might fail - For more information, read this SO post and Dan Guzman's blog post it links to.

edited Sep 28 '18 at 21:23

answered Sep 28 '18 at 18:33

Zohar Peled

79,642
10
69
121

what is PK field into last where clause? `WHERE [TN].[PK] IS NULL`? – Pepe Sep 28 '18 at 18:42
I try this but I still have same problem – Pepe Sep 28 '18 at 18:59
Shouldn't "[TN].[EmpKey] = [ANT].[EmpKey]" be "[TN].[EmpKey] = [E].[EmpKey]"? – UnhandledExcepSean Sep 28 '18 at 19:34
I'm really confused there... I don't have an idea what is `[TN].[PK] `@UnhandledExcepSean – Pepe Sep 28 '18 at 20:21
Yes, "[TN].[EmpKey] = [ANT].[EmpKey]" be "[TN].[EmpKey] = [E].[EmpKey]". And [TN].[PK] - Replace `PK` with the primary key column of the `TN` table (any non-nullable column will do, the primary key is also indexed so it will have a better execution plan. – Zohar Peled Sep 28 '18 at 21:22

Validate if value exists before insert row

1 Answers1