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)