How to retrieve duplicate rows in a table?
How to delete the duplicate entries in a table?
Answer Posted / mohit d jethva
///////////Cursor for delete duplicate record in
table//////////
DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int
DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [Example] GROUP BY [ID],
[NAME] HAVING COUNT([ID]) > 1
OPEN CUR_DELETE
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE TOP(@COUNT -1) FROM [Example] WHERE ID = @ID
FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END
CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE
Mohit D Jethva
Is This Answer Correct ? | 7 Yes | 4 No |
Post New Answer View All Answers
How do you set a trace flag in sql server?
Can primary key be a foreign key?
What is lookup override?
What is the purpose of the model database?
What are the different types of cursor?
What is compression - row-level and page-level compression?
What is explicit cursors?
What are the properties of primary key?
What are parameterized reports?
What are user-defined functions (udfs) in sql server?
What is check constraint in sql server?
Can you explain what are the restrictions applicable while creating views? : SQL Server Architecture
What is statement level trigger?
What is exclusive locks?
Give me a SQL Query to find out the second largest company?