Removing Duplicates in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing Duplicates in SQL Server

Author

Topic

redbrad0
Posting Yak Master

176 Posts

Posted - 2004-10-14 : 23:31:47

I know there is a article here about this, but it does not work in my data. Here is my table layout...


FeedData
     Data_ID (int identity key)
     Data_FeedID (int)
     Data_Data (text)
     Data_Error (nvarchar(500))
     Data_Date (nvarchar(20))

My problem is that the Data_Data column is a Data Type text which seems to be the problem. I want to remove anything that has the exact same data in the table. Below are some sample SQL Statments I have tried and do not work because you cant groupby the Data_Data table. Can anyone help me out here?

Quality Web Hosting & Design
http://www.eznthosting.com

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-14 : 23:35:41

[code]
Create Table #blah (id int identity(1,1), blah text)
Insert Into #blah
Select 'This is text data1'
Union All Select 'This is text data2'
Union All Select 'This is text data1'
Union All Select 'This is text data3'

Select * From #blah

Delete A from #blah A Inner Join #blah B On A.blah like B.blah and A.id>B.id

Select * From #blah

Drop Table #blah
[/code]

Corey

redbrad0
Posting Yak Master

176 Posts

Posted - 2004-10-15 : 00:05:19

Thanks so much for the reply back cory but hate to tell you I am a little lost.... What is all the Union All Select? Can you help me with my query?


Create Table #Feeds_Data_Temp (Data_ID int identity(1,1), Data_FeedID int, Data_Data text, Data_Error nvarchar(500), Data_DateArchived nvarchar(20))

-- Union Data Here ??

Where is this code is it reinserting back into the Old Table?

Quality Web Hosting & Design
http://www.eznthosting.com

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-15 : 02:06:40

In the below code Corey was only creating his instance of FeedData table for his example.

quote:

Originally posted by Seventhnight


Create Table #blah (id int identity(1,1), blah text)
Insert Into #blah
Select 'This is text data1'
Union All Select 'This is text data2'
Union All Select 'This is text data1'
Union All Select 'This is text data3'

Select * From #blah

The code you would need is just this....(replace blah's with your corresponding table and columns names...)

quote:
Originally posted by Seventhnight

Delete A from #blah A Inner Join #blah B On A.blah like B.blah and A.id>B.id

as

Delete A from FeedData A Inner Join FeedData B On A.Data_Data like B.Data_Data and A.Data_ID>B.Data_ID

Hemanth Gorijala
BI Architect / DBA...
Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources