You may come across a need to explicitly set an IDENTITY Key value in your insert statement. I recently came across this need when I wanted to add some columns to a Dimension table to account for any NULL values I was transforming.
Take into account my DimDates table:
CREATE TABLE [dbo].[DimDates](
[DateKey] [int] IDENTITY(1,1) NOT NULL,
[Date] [datetime] NOT NULL,
[DateName] [nvarchar](50) NULL,
[Month] [int] NOT NULL,
[MonthName] [nvarchar](50) NOT NULL,
[Quarter] [int] NOT NULL,
[QuarterName] [nvarchar](50) NOT NULL,
[Year] [int] NOT NULL,
[YearName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)
Notice the DateKey field is a primary key and is an IDENTITY field, so that it automatically increments the primary key to the next available integer when a new row is inserted.
If you were to try to explicitly set that field value, it would result in the following error:
INSERT INTO
[dbo].[DimDates]
(
[DateKey]
, [Date]
, [DateName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
, [YearName]
)
VALUES
(
–1
, ‘1/1/1900’
, ‘Date not specified’
, –1
, ‘Month not specified’
, –1
, ‘Quarter not specified’
, –1
, ‘Year not specified’
)
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘DimDates’ when IDENTITY_INSERT is set to OFF.
You can get past this limitation by using the IDENTITY_INSERT statement as follows:
SET IDENTITY_INSERT [dbo].[DimDates] ON
INSERT INTO
[dbo].[DimDates]
(
[DateKey]
, [Date]
, [DateName]
, [Month]
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
, [YearName]
)
VALUES
(
–1
, ‘1/1/1900’
, ‘Date not specified’
, –1
, ‘Month not specified’
, –1
, ‘Quarter not specified’
, –1
, ‘Year not specified’
)
SET IDENTITY_INSERT [dbo].[DimDates] OFF
(1 row(s) affected)