sql-server - 在插入之前检查列是否包含值 T-SQL

Question

我有一个这样的存储过程：

SET NOCOUNT ON;

BEGIN TRAN;
    INSERT INTO [TaskNotification] (TaskId, EmpKey)
        SELECT 
            TaskId, E.EmpKey
        FROM 
            @TaskNotificationTableType TNT
        INNER JOIN 
            Employee E ON E.EmpGuid = TNT.EmpGuid;
COMMIT TRAN

例如，我想知道如何在插入值之前检查我的列E.EmpKey值是否存在

IF (E.EmpKey EXISTS) 
    // DO NOTHING
ELSE
    INSERT INTO [TaskNotification] (TaskId, EmpKey)
        SELECT 
            TaskId, E.EmpKey
        FROM 
            @TaskNotificationTableType TNT
        INNER JOIN 
            Employee E ON E.EmpGuid = TNT.EmpGuid;

我怎样才能实现它？问候

标签： sql-servertsql

Answer 1

如果您的意思是EmpKey不必为NULL，Employee那么过滤就WHERE足够了。

INSERT INTO [TaskNotification] (TaskId, EmpKey)
SELECT 
    TaskId, E.EmpKey
FROM 
    @TaskNotificationTableType TNT
INNER JOIN 
    Employee E ON E.EmpGuid = TNT.EmpGuid
WHERE
    E.EmpKey IS NOT NULL;

TaskNotification如果您的意思是如果记录已经存在，您不想在其中插入记录，请使用WHERE NOT EXISTS.

INSERT INTO [TaskNotification] (TaskId, EmpKey)
SELECT 
    TaskId, E.EmpKey
FROM 
    @TaskNotificationTableType TNT
INNER JOIN 
    Employee E ON E.EmpGuid = TNT.EmpGuid
WHERE
    NOT EXISTS (SELECT 'not in TaskNotification yet' FROM [TaskNotification] T
                WHERE T.EmpKey = E.EmpKey)