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

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

Edit

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.