SQL- Shortcut way to delete duplicate records from a table

I have discussed earlier in one of my post how to delete duplicate records from a table . But recently i came up with even shorter way to delete duplicate records.

Consider a StudentInfo Table as shown  below for demonstration.

SELECT * FROM StudentInfo

StudentInfo Table

Step1:
 First we will create a temporary table '#temp' with same structure  as that of original table 
'StudentInfo' and insert all distinct rows from the 'StudentInfo' table into temporary table as follows:


 SELECT DISTINCT * INTO #temp FROM StudentInfo

Let us check the #temp table


SELECT * FROM #temp

We see that all duplicate records are deleted and only distinct records are made available in temporary table.

Step2: 
Now we will simply delete the existing record in StudentInfo and then insert all records from temp table into StudentInfo table.


DELETE FROM StudentInfo

INSERT INTO StudentInfo 
SELECT * FROM #temp

Distinct records has been inserted into original table 'StudentInfo'. Lets us check out.

SELECT * FROM STUDENTINFO

So we see that , with just two simple steps , we are able to delete duplicate records from a table.

Click to check out other way to delete duplicate records from a table