TSQL script - CTE to remove duplicate rows

TSQL script - CTE to remove duplicate rows

// Creating Table and Inserting Data //

CREATE TABLE #Table (C1 INT,C2 VARCHAR(10))

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (2,'Oracle')

// Retrieving Data //

SELECT * FROM #Table


-- Delete Duplicate rows --
  
;WITH Delete_Duplicate_Row_cte

AS (SELECT ROW_NUMBER()OVER(PARTITION BY C1, C2 ORDER BY C1,C2) ROW_NUM,*

FROM #Table )

DELETE FROM Delete_Duplicate_Row_cte WHERE ROW_NUM > 1

-- Retrivieng Records after deleting duplicate rows --

SELECT * FROM #Table

 -- Drop the table --
DROP TABLE #Table