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
– Alvaro Parra, Commented Sep 28, 2018 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
– Pepe, Commented Sep 28, 2018 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
– Sean Lange, Commented Sep 28, 2018 at 18:21

Zohar Peled · Accepted Answer · 2018-09-28 21:23:35Z

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, 2018 at 21:23

answered Sep 28, 2018 at 18:33

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pepe Over a year ago

what is PK field into last where clause? WHERE [TN].[PK] IS NULL?

Pepe Over a year ago

I try this but I still have same problem

UnhandledExcepSean Over a year ago

Shouldn't "[TN].[EmpKey] = [ANT].[EmpKey]" be "[TN].[EmpKey] = [E].[EmpKey]"?

Pepe Over a year ago

I'm really confused there... I don't have an idea what is [TN].[PK] @UnhandledExcepSean

Zohar Peled Over a year ago

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.

Collectives™ on Stack Overflow

Validate if value exists before insert row

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related