Hi
I have inherited a web app with the following table structure, and need to
produce a table without any duplicates. Email seems like the best unique
identifier - so only one of each e-mail address should be in the table.
Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able to
get a duplicate count working:
select Email, count(*) as UserCount
from dbo.Members
group by Email
having count(*) > 1
order by UserCount desc
But the methods for create a new table without duplicates fail. My code for
the 2nd method is:
sp_rename 'Members', 'temp_Members'
select distinct *
into Members
from temp_Members
Table....
CREATE TABLE [dbo].[Members] (
[MemberID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,
[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,
[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,
[DateAdded] [smalldatetime] NOT NULL ,
[SendMail] [smallint] NOT NULL
) ON [PRIMARY]
GO
Thanks B.