team2005
asked on
How to remove duplicates when no primary key
Hi!
How can i remove duplicates from a table.
When i dont have a primary key ?
How can i remove duplicates from a table.
When i dont have a primary key ?
You can do he following way, if the the table is small and is not a part of replication, mirroring and Logshipping and does not have any references(Foreignkey)
DECLARE @t TABLE
(ID INT, C INT, C2 INT, C3 INT)
INSERT INTO @t
SELECT 1,1,2,4
INSERT INTO @t
SELECT 1,1,2,4
INSERT INTO @t
SELECT 1,1,2,4
INSERT INTO @t
SELECT 3,1,2,4
INSERT INTO @t
SELECT 1,2,2,4
INSERT INTO @t
SELECT 1,2,2,4
SELECT * FROM @t
-- in partition by clause, include all columns
SELECT ID,C,C2,C3
INTO #temp
FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY ID, C, C2, C3 ORDER BY ID) RID,*
FROM @t
) T
WHERE RID = 1
DELETE FROM @t
INSERT INTO @t
SELECT * FROM #temp
SELECT * FROM @t
DROP TABLE #temp
hm, @tapanpattanaik that link looks pretty good, Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Please check this link, here is answer to your question.
Removing Duplicates from a Table in SQL Server:(Tables with no primary key)
http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/