What is Inserting data into an identity column

When the IDENTITY property is defined for a column, INSERT statements into the table generally don’t specify a value for the IDENTITY column. The database engine generates a value using the next available value for the column.

For example, you could insert a row into the Sales.Promotion table without specifying a value for the PromotionID column:

SQLCopy

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Notice that even though the VALUES clause doesn’t include a value for the PromotionID column, you don’t need to specify a column list in the INSERT clause – Identity columns are exempt from this requirement.

If this row is the first one inserted into the table, the result is a new row like this:

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Notes

1

Clearance Sale

2021-01-01T00:00:00

23

0.1

10% discount

When the table was created, no seed or increment values were set for the IDENTITY column, so the first row is inserted with a value of 1. The next row to be inserted will be assigned a PromotionID value of 2, and so on.