More Stuff
Sometime we have a very challenging task, Duplicate data in database store but we have to keep one data safe and need to be delete other data , duplicate data may store sometime because of restore database,
sometime migrating data from another database, some time our leakage so that we have to find and delete these duplicate from the database to purify the database
delete duplicate data from database means keep one record safe and delete other duplicate data
Delete Duplicate data from database and keep one record in Sql Server we need to have one primary key record
Syntax
Here Don't change the other. change to column name by your desire or need delete data. and change the table name by your table
For eg
sometime migrating data from another database, some time our leakage so that we have to find and delete these duplicate from the database to purify the database
delete duplicate data from database means keep one record safe and delete other duplicate data
Delete Duplicate data from database and keep one record in Sql Server we need to have one primary key record
Syntax
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY [column name ]ORDER BY [column Name])
As RowNumber,* FROM [TableName]
)
DELETE FROM tblTemp where RowNumber > 1
Here Don't change the other. change to column name by your desire or need delete data. and change the table name by your table
For eg
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY studentrollno ORDER BY studentrollno )
As RowNumber,* FROM tblstudent
)
DELETE FROM tblTemp where RowNumber > 1
0 Comments