alter indentity primary key

-- Create products table.

CREATE TABLE products ( id int IDENTITY PRIMARY KEY , product varchar ( 40))

GO

-- Inserting values into products table.

INSERT INTO products ( product ) VALUES ( 'screwdriver' )

INSERT INTO products ( product ) VALUES ( 'hammer' )

INSERT INTO products ( product ) VALUES ( 'saw' )

INSERT INTO products ( product ) VALUES ( 'shovel' )

GO

 

 

-- Create a gap in the identity values.

DELETE products

WHERE product = 'saw'

GO

 

SELECT *

FROM products

GO

 

-- Attempt to insert an explicit ID value of 3;

-- should return a warning.

INSERT INTO products ( id , product ) VALUES ( 3, 'garden shovel' )

GO

-- SET IDENTITY_INSERT to ON.

SET IDENTITY_INSERT products ON

GO

 

-- Attempt to insert an explicit ID value of 3

INSERT INTO products ( id , product ) VALUES ( 3, 'garden shovel' )

GO

 

SELECT *

FROM products

GO

-- Drop products table.

DROP TABLE products

GO