After good 1 day in San Antonio now once again I am in
the back seat of the car and going to Austin. Anyway, I am reading this great
book by Ken Henderson about T-SQL programing and in this book he has mentioned a
neat trick to remove duplicates using the INSERT statement and index.
CREATE TABLE #test1 (c1 varchar(20), c2 int, notes text)
INSERT INTO #test1 VALUES('AzamSharp',1,'Something special for everyone')
INSERT INTO #test1 VALUES('AzamSharp',1,'Not so special after all')
INSERT INTO #test1 VALUES('JohnDoe',2,'Hey you');
INSERT INTO #test1 VALUES('AzamSharp',3,'Ahh home sweet home')
First I created a simple table with three columns. And now I will
create another table with the same structure.
CREATE TABLE #test2 (c1 varchar(20), c2 int, notes text)
Now, the final thing is to create the index on the #test2 table and
insert the data from the #test1 table into the #test2 table.
CREATE UNIQUE INDEX removeduplicaterows ON #test2 (c1, c2)
WITH IGNORE_DUP_KEY
INSERT INTO #test2
SELECT * FROM #test1
This is it. And now the new table test2 will contain all the rows
except the duplicate rows.
powered by IMHO 1.3