Insert Data on Identity Column

After lots of  this and that, finally i got solution to insert data on Identity Column with starting with last identity_number.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DuplicateTable](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NOT NULL,
[ManagerID] [int] NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[DuplicateTable] VALUES (1209, 16, N'M', N'M', 21, 30)
INSERT [dbo].[DuplicateTable] VALUES (1030, 6, N'S', N'M', 42, 41)
INSERT [dbo].[DuplicateTable] VALUES (1002, 12, N'M', N'M', 2, 21)
INSERT [dbo].[DuplicateTable] VALUES (1290, 3, N'S', N'M', 48, 80)
INSERT [dbo].[DuplicateTable] VALUES (1009, 263, N'M', N'M', 9, 24)
INSERT [dbo].[DuplicateTable] VALUES (1028, 109, N'S', N'M', 40, 40)
INSERT [dbo].[DuplicateTable] VALUES (1070, 21, N'S', N'F', 82, 61)
INSERT [dbo].[DuplicateTable] VALUES (1071, 185, N'M', N'F', 83, 61)
INSERT [dbo].[DuplicateTable] VALUES (1005, 3, N'M', N'F', 5, 22)
INSERT [dbo].[DuplicateTable] VALUES (1076, 185, N'S', N'M', 88, 64)


Select * from DuplicateTable

O/p
---------------------------------------------------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours

---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
10 1076 185 S M 88 64

Delete from DuplicateTable where EmployeeID =10

Select * from DuplicateTable

O/p
---------------------------------------------------------------------------------------------------------------------------------------
EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22


INSERT [dbo].[DuplicateTable] VALUES (1129, 173, N'M', N'M', 41, 40)


Select * from DuplicateTable

O/p
------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
11 1129 173 M M 41 40

Delete from DuplicateTable where EmployeeID =11

DBCC CHECKIDENT (DuplicateTable, reseed, 9)

INSERT [dbo].[DuplicateTable] VALUES (1129, 173, N'M', N'M', 41, 40)


Select * from DuplicateTable
o/p
---------------------------------------------------------------------------------------------------------------------------------------

EmployeeID ContactID ManagerID MaritalStatus Gender VacationHours SickLeaveHours
---------------------------------------------------------------------------------------------------------------------------------------
1 1209 16 M M 21 30
2 1030 6 S M 42 41
3 1002 12 M M 2 21
4 1290 3 S M 48 80
5 1009 263 M M 9 24
6 1028 109 S M 40 40
7 1070 21 S F 82 61
8 1071 185 M F 83 61
9 1005 3 M F 5 22
10 1129 173 M M 41 40

You got it...!!!!