jun.merencilla (9/22/2010)
good article. but i would not do this simply because there is a more straightforward way.
select distinct *
into #Emp_Details
from Emp_Details
truncate table Emp_Details
insert into Emp_Details
select * from #Emp_Details
though some of you guys may raise your eyebrows the moment you see temp tables, this code gets the job done without resorting to complex code.
Not really practical for production.
1. Need to take whole database offline when the table vanishes.
2. If it is a large table & you are using Log Reader based technology (ie: Replication, Mirroring, Change Data Capture, Log Shipping, Incremental loads into your SSAS cubes etc) this will fill your log & create a load on the downstream destinations.
3. Need to drop any Declarative Referential Integrity constraints & other things that schema binding will prevent. So also need to do all that schema work before a truncate table.
So perhaps instead of truncate. Consider using the MERGE command to remove those duplicate rows.