Do you ever wonder how to INSERT values in IDENTITY column or fill the gap of missing value of IDENTITY column! Then you tuned in the right channel! We are going to explore the IDENTITY function. At first look at the syntax:
Syntax
IDENTITY [ (seed , increment) ]
Arguments
- seed: Is the value that is used for the very first row loaded into the table.
- increment: Is the incremental value that is added to the identity value of the previous row that was loaded.
Now we are going to do following step-by-step: creating a table with IDENTITY, INSERT sample data, delete few rows then try to INSERT value in IDENTITY column. Let's explore:
-- Drop table if it already exists.
IF OBJECT_ID('Test1', 'U') IS NOT NULL
DROP TABLE Test1;
GO
-- Create the table
CREATE TABLE Test1(
pk_id int not null identity(1,1),
name varchar(10) default ('Mehedi')
)
GO
-- Populate with 100 sample data
INSERT INTO Test1 default values
GO 100
-- Examine the data
SELECT * FROM Test1
GO
-- Delete the data with pk_id between 85 to 90
DELETE FROM Test1 WHERE pk_id BETWEEN 85 AND 90
GO
-- Examine the data
SELECT * FROM Test1
GO
Data within 85 to 90 are missing due to above DELETE statement. Now INSERT value in IDENTITY column.
-- Disable the IDENTITY column and re-enter
SET IDENTITY_INSERT Test1 ON
INSERT INTO Test1(pk_id,name) VALUES(88, 'Hasan')
SET IDENTITY_INSERT Test1 OFF
GO
-- Examine data
SELECT * FROM Test1
GO
Data with pk_id 88 is just inserted.
Happy TSQLing!