SQL Server "not exists" on unique key attempts to insert rows?

In this example, table_a has a unique key constraint on visit_id.

Why doesn't and not exists select *... prevent SQL Server from attempting to insert rows from table_b with row_id's that already exist in table_a?

insert table_a
    (visit_id, value1, value2)
        select visit_id, value1, value2, is_new
        from table_b src
        where is_new = 1
            and not exists
                (select *
                from table_a
                where visit_id = src.visit_id)

I am getting the error:

Msg 2601, Level 14, State 1, Line ... Cannot insert duplicate key row in object 'dbo.table_a' with unique index 'IDX_table_a'.

The context here is an ETL routine that inserts new visits only, and updates existing visits in place. I know I can write it differently, but I don't understand why this statement isn't evaluated such that it wouldn't attempt to insert rows with existin开发者_如何学编程g visit_id's.

Is it possible that table_b has multiple rows with any given visit_id value? In other words, the violation may not be happening because a row in table_b is already in table_a, but rather because you are trying to insert two new duplicates.

You can check by asking:

SELECT visit_id, COUNT(*)
  FROM dbo.table_b
  GROUP BY visit_id
  HAVING COUNT(*) > 1;

Then, how you deal with it is up to you - you need to decide how to determine which row to keep.