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:

CREATETABLE[dbo].[DimDates](

[DateKey][int]IDENTITY(1,1)NOTNULL,

[Date][datetime]NOTNULL,

[DateName][nvarchar](50)NULL,

[Month][int]NOTNULL,

[MonthName][nvarchar](50)NOTNULL,

[Quarter][int]NOTNULL,

[QuarterName][nvarchar](50)NOTNULL,

[Year][int]NOTNULL,

[YearName][nvarchar](50)NOTNULL,

PRIMARYKEYCLUSTERED

(

[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:

INSERTINTO

[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:

SETIDENTITY_INSERT[dbo].[DimDates]ON

INSERTINTO

[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)

Advertisements