Violation of primary key in an INSERT INTO statement - does entire statement get terminated?

Question

I have a statement to update a table from another table

INSERT INTO table1 SELECT * FROM table2

Due to some rubbish test data still being in table1 (but a scenario that may occur in live, anything is possible), I get the error

Violation of PRIMARY KEY constraint 'PK_xxx'. Cannot insert duplicate key in object 'table1'
The statement has been terminated

If the SELECT from table2 returns 100 rows, is only the violating insert not committed to table1 or is the ENTIRE INSERT INTO statement not committed/rolled back due to the PK violation?

Answer 1

The entire statement is not committed. It's easy enough to test, like this:

Create Table #Target (Id Int Primary Key)
Insert Into #Target Values(1)
Insert Into #Target Values(3)
Insert Into #Target Values(5)
Insert Into #Target Values(7)

Create Table #Source (Id Int)
Insert Into #Source Values(1)
Insert Into #Source Values(2)
Insert Into #Source Values(3)
Insert Into #Source Values(4)
Insert Into #Source Values(5)
Insert Into #Source Values(6)
Insert Into #Source Values(7)
Insert Into #Source Values(8)

Insert Into #Target(Id)
Select Id From #Source

Select * From #target

Drop Table #Target
Drop Table #Source

The code above create a target table with a primary key. Then it creates a source table with the same column but different values. Then a similar command to the one you posted is executed where we insert rows from the source table to the target table.

Then we select from the target table. As you can see, only the original values are there.

If you use this code instead, only the missing rows will be inserted.

Insert
Into   #Target
Select  #Source.*
From    #Source
        Left Join #Target
            On #Source.Id = #Target.Id
Where   #Target.Id Is NULL