Inserting rows using OUTPUT.. INTO.. a table with Identity column

Let`s suggest you have some simple archiving process that implemented as deleting rows
from the source table and inserting the deleted rows to the archive table that has
same schema as the source table.

For example:
Let`s create 2 tables with SAME schema (SourceTable and TargetTable)
and insert 3 rows to the "SourceTable":

CREATE TABLE dbo.SourceTable
(
  CustomerID INT NOT NULL IDENTITY(1,1),
  CustomerName VARCHAR(50) NOT NULL
)
GO

CREATE TABLE dbo.TargetTable
(
CustomerID INT NOT NULL IDENTITY(1,1),
CustomerName VARCHAR(50) NOT NULL
)
GO

INSERT INTO dbo.SourceTable(CustomerName)
SELECT 'Customer1' as CustomerName
UNION ALL
SELECT 'Customer2' as CustomerName
UNION ALL
SELECT 'Customer3' as CustomerName
GO

Pay attention that the "CustomerID" column defined as IDENTITY in both tables.
Now we will delete all rows from the "SourceTable" and by using the OUTPUT..INTO
insert them to the "TargetTable".

DELETE  FROM dbo.SourceTable
OUTPUT deleted.CustomerID,deleted.CustomerName
INTO dbo.TargetTable(CustomerID,CustomerName)
GO

We will not get any errors, but we should.
We should get an error saying something like below:
"Cannot insert explicit value for identity column in table 'TargetTable' when IDENTITY_INSERT is set to OFF."


Why we should get the error?
Because we are trying to insert explicit values to column
that defined as Identity
and we did not set the IDENTITY_INSERT to ON in the TargetTable.
But in our case, we did not get this error!
And the rows were successfully inserted.
The question is why?


If you can explain it, please write it in the comments.

How to generate autonumber (identity) value in existing table in single Update statement

Suppose you have some table and you want to populate
some column with AutoNumber (Identity) value.
For this task we can use
SET @variable = column = expression.
Consider the following example:

DECLARE @TmpTable TABLE
(
RowID INT NULL,
EmpName VARCHAR(10) NOT NULL
)
INSERT INTO @TmpTable(EmpName)
SELECT 'Dan' UNION ALL SELECT 'Greg' UNION ALL SELECT 'Jeck'
SELECT * FROM @TmpTable

The result of the Select statement will be:
---------------------------------------------
RowID EmpName
NULL Dan
NULL Greg
NULL Jeck

Now we declare @Count valiable and use it in our Update statement like this:

DECLARE @Count INT
SET @Count = 0
UPDATE @TmpTable
SET @Count = RowID = @Count +1

SELECT * FROM @TmpTable

The result of the Select statement will be:
---------------------------------------------
RowID EmpName
1 Dan
2 Greg
3 Jeck

What happening is that the @count variable gets every time
(every row) the value of the RowID column.

From the Books OnLine:
SET @variable = column = expression sets the variable to the same value as the column.
This differs from SET @variable = column, column = expression, which sets the variable
to the pre-update value of the column.

I think not all DBAs aware of this syntax(feature).