Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly. Create a table called ATTENDANCE by using the following code:
CREATE TABLE [dbo].[ATTENDANCE](
[EMPLOYEE_ID] [varchar](50) NOT NULL,
[ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY]
Now insert some data into this table.
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A001',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A002',CONVERT(DATETIME,'01-01-11',5))
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
('A003',CONVERT(DATETIME,'01-01-11',5))
After inserting the data, check the data of the below table. If we grouped the employee_id and attendance_date, then A001 and A002 become duplicates.
EMPLOYEE_ID ATTENDANCE_DATE
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01
So how can we delete those duplicate data?
Solution
First, insert an identity column in that table by using the following code:
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)
Now the table data will be like the following table:
EMPLOYEE_ID ATTENDANCE_DATE AUTOID
A001 2011-01-01 1
A001 2011-01-01 2
A002...