Insert Values Into An Identity Column

Posted: November 20, 2011 in SQL Server 2005

To manually insert a record with a identity value, you can do the following:

-- Create table with identity column
CREATE TABLE dbo.MyTable 
            (my_id    INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	          my_value VARCHAR(20) NOT NULL);
	          
-- Insert records into table
INSERT INTO dbo.MyTable (my_value) VALUES ('One');	          
INSERT INTO dbo.MyTable (my_value) VALUES ('Two');
INSERT INTO dbo.MyTable (my_value) VALUES ('Three');

-- Select records from table.  This should have identity's 1, 2 and 3
SELECT * FROM dbo.MyTable;

-- Insert a record with a new identity value 5
SET IDENTITY_INSERT dbo.MyTable ON
INSERT dbo.MyTable(my_id, my_value) VALUES (5, 'Five')
SET IDENTITY_INSERT dbo.MyTable OFF

-- Select records from table.  This should have identity's 1, 2, 3 and 5.  Note that we have jumped over identity 4
SELECT * FROM dbo.MyTable;

-- Now insert a new records into the table with identity back on
INSERT INTO dbo.MyTable (my_value) VALUES ('Six');

-- Select records from table.  Since we inserted a identity value of 5, the new record should seed after 5
SELECT * FROM dbo.MyTable;

Advertisements
Comments
  1. Johnd518 says:

    I appreciate you sharing this article.Thanks Again. Really Cool. bcaebdgkdbad

  2. Johne407 says:

    I really enjoy reading on this website, it holds great articles. Don’t put too fine a point to your wit for fear it should get blunted. by Miguel de Cervantes. kbdebedddgda

  3. nzgeek says:

    Your welcome 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s