I’m creating an SQL script to seed my SQL Server database, the tables include a primary key with autoincrement set (i.e. IDENTITY(1,1)). Inserting data within supplying a primary key works fine, but if you decide to seed the primary key data as well then I need to make a couple of changes to my SQL script.

Why would I want to seed the primary key if it’s autoincrementing, you may ask. The reason is that I intend to also seed some of the relationship data as well and this ofcourse means I already have the table’s primary key value (because I set it) and thus can easily create the relationship links.

What you need to do is wrap your INSERTs for a specific table in, so for example below we have a Country table and the primary key is Id.

SET IDENTITY_INSERT [dbo].[Country] ON

INSERT INTO [dbo].[Country] ([Id], [Name]) 
VALUES (1, 'Australia')

SET IDENTITY_INSERT [dbo].[Country] OFF