Explicitly Insert in an Identity Column in SQL

1. Explicitly Insert in an Identity Column in SQL
–>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

select * from products
— Create a gap in the identity values.
DELETE products WHERE product = ‘saw’
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
— 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

Advertisements

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