Saturday 12 January 2013

What is IDENTITY_INSERT in SQL Server?

its a property in SQL server by turning it ON It allows a user to insert explicit values in an identity column of a table, SQL server considers the new inserted value as identity.The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

let see an example how it works

create gap to see deletion of an identity value



an attempt to insert it again



turn on IDENTITY_INSERT




Note :At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

here is the complete code

-- Create BOOKS table.
CREATE TABLE BOOKS (id int IDENTITY PRIMARY KEY, NAME varchar(40))
GO
-- Inserting values into BOOKS table.
INSERT INTO BOOKS (NAME) VALUES ('C++')
INSERT INTO BOOKS (NAME) VALUES ('.NET')
INSERT INTO BOOKS (NAME) VALUES ('JAVA')
INSERT INTO BOOKS (NAME) VALUES ('SQL')
GO

SELECT * FROM BOOKS
GO

-- Create a gap in the identity values.
DELETE BOOKS
WHERE NAME = 'JAVA'
GO

SELECT *
FROM BOOKS
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO BOOKS (id, NAME) VALUES(3, 'PROGRAMMING LANGUAGES')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT BOOKS ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO BOOKS (id, NAME) VALUES(3, 'PROGRAMMING LANGUAGES')
GO

SELECT *
FROM BOOKS
GO
-- Drop BOOKS table.
DROP TABLE BOOKS

No comments:

Post a Comment