Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

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

Posted on Monday, December 26, 2005 4:07 PM | Back to top


Comments on this post: Using INSERT to remove duplicate rows

# re: Using INSERT to remove duplicate rows
Requesting Gravatar...
You can also use

INSERT INTO #test2
SELECT * FROM #test1 GROUP BY C1,C2

provided the entire row is duplicated and not just the first 2 columns.
Left by Kai on Jan 11, 2006 6:46 AM

# re: Using INSERT to remove duplicate rows
Requesting Gravatar...
clear & simple. A+
Left by janK on Jul 06, 2006 12:45 AM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net