0

If I have the following table definition:

CREATE TABLE [dbo].[Application] (
    [ApplicationId]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.Application] PRIMARY KEY CLUSTERED ([ApplicationId] ASC)
);

Is it possible to insert my own test data and override the identity column? The reason I am asking is because I have several tables I need to populate like this and I want to get the ID columns in each to match foreign keys?

If this is not possible then could I insert data and then later change it to an identity column? If anyone can give me an example how to do that I would be very happy.

  • Yes, turn on `IDENTITY_INSERT` for that table and you'll be able to insert into the ID column (remember to turn it off again afterwards!) – Bridge Feb 26 '13 at 13:16
  • I am sorry but how do I turn it on and off and then how would I add it later with a different start value? –  Feb 26 '13 at 13:17

2 Answers2

4
SET IDENTITY_INSERT Application ON

INSERT INTO Application(ApplicationId, Name)
VALUES (3, 'Third Row')

SET IDENTITY_INSERT Application OFF
Kevin DiTraglia
  • 25,746
  • 19
  • 92
  • 138
  • 1
    be sure to run DBCC CHECKIDENT( , RESEED ) afterwards to reset the next seed value for the table – qujck Feb 26 '13 at 13:27
  • @qujck - How does this work? –  Feb 26 '13 at 13:40
  • @Anne Running that command (the one posted by qujck) will allow you to change where the new identities will start from when you are doing normal identity inserts. – Kevin DiTraglia Feb 26 '13 at 13:43
1
SET IDENTITY_INSERT Application ON

[Do your insert]

SET IDENTITY_INSERT Application OFF
Hassan Voyeau
  • 3,383
  • 4
  • 22
  • 24