Inserting value into identity column

When moving data from one database instance to another in SQL Server 2000 or SQL Server 2005 you can create insert statements to do this. The problem comes when you try to keep relationships between tables or even just keep identity columns on one table the same as the source.

Let’s assume you have only one table to move and it is defined as follows:

CREATE TABLE [CustomerCategory](

[CustomerCategoryID] [int] IDENTITY(1,1) NOT NULL,

[Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[RegionID] int NOT NULL

) ON [PRIMARY]

Assume the tabe can be scripted as follows and has two rows:

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (45, 'Computer', 'COMPUTER', 1)

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (46, 'Printer', 'PRINTER', 1)

Notice that the CustomerCategoryID is an identity column and therefore SQL Server won’t let you do the above.

To get around this you need to use the SQL Server specific option IDENTITY_INSERT

Here is the final solution for inserting the data into the new table and keeping the identity column values in tact.

set IDENTITY_INSERT CustomerCategory ON

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (1, 'Computer', 'COMPUTER', 1)

INSERT INTO CustomerCategory (CustomerCategoryID, [Name], Code, RegionID)

VALUES (2, 'Printer', 'PRINTER', 1)

set IDENTITY_INSERT CustomerCategory OFF

This type of thing comes in handy when moving database tables from dev to QA or QA to Production.