EXPLICIT VALUE FOR IDENTITY IN A TABLE IN SQL SERVER 2005

CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

SELECT * FROM EMPLOYEE

INSERT INTO EMPLOYEE
SELECT ‘Pawan’ , 180000
UNION ALL
SELECT ‘Gaurav’ , 80000
UNION ALL
SELECT ‘Saurabh’ , 80000
UNION ALL
SELECT ‘Pawan’ , 830000
UNION ALL
SELECT ‘Neelam’ , 80000
UNION ALL
SELECT ‘Pawan’ , 80000
UNION ALL
SELECT ‘Sharlee’ , 820000
UNION ALL
SELECT ‘Pawan’ , 80000
UNION ALL
SELECT ‘Rahul’ , 980000

–Check data is inserted.
SELECT * FROM EMPLOYEE

–USE Following code

SET IDENTITY_INSERT dbo.EMPLOYEE ON
GO
INSERT INTO EMPLOYEE ( id , EMPNAME , SALARY ) VALUES ( 21 , ‘Shyam’ , 10000 )

–Use this to off the identiy insert
SET IDENTITY_INSERT dbo.EMPLOYEE OFF

INSERT INTO EMPLOYEE
SELECT ‘XXXXXX’ , 180000

–Check data is inserted.
SELECT * FROM EMPLOYEE

Pawan Kumar
Pawankkmr@hotmail.com

Advertisements