Dupes 
Author Message
 Dupes
    I'm importing data from an outside source that does contain some dupes
and I need to eliminate them. What structure should I use? Should I use an
ID column and insert the dupes, then get rid of them. What is the best
method?
Thank You

Paul



Sun, 05 Mar 2006 01:09:05 GMT
 Dupes

Refer to this url:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444

--if you have primary key defined in the table then you can have query as
specified in below example
--where filename and filepath are repeating

create table [file](id int    ,FileName          varchar(500),FilePath
varchar(500))
insert into [file]
select 1   ,'DailyReport','C:\...\dailyreport.rpt' union all
select 2   ,'DailyReport','C:\...\dailyreport.rpt' union all
select 3   ,'DailyReport','C:\...\dailyreportNew.rpt' union all
select 4   ,'DailyReport','C:\...\dailyreportNew.rpt' union all
select 5   ,'MonthlyReport','C:\...\monthlyreport.rpt' union all
select 6   ,'YearlyReport','C:\...\yearlyreport.rpt'

--query to keep one record out of all the duplicates assuming id as a unique
key.

delete from [file]
where id not in
(select min(id) from [file] b where [file].filename=b.filename and
[file].filepath=b.filepath )

- Vishal

Quote:
>     I'm importing data from an outside source that does contain some dupes
> and I need to eliminate them. What structure should I use? Should I use an
> ID column and insert the dupes, then get rid of them. What is the best
> method?
> Thank You

> Paul



Sun, 05 Mar 2006 01:20:13 GMT
 Dupes
Or, you could load the data with duplicates into a staging table and then
select the distinct rows out of the staging table into your main table


Quote:
> Refer to this url:
> http://support.microsoft.com/default.aspx?scid=KB;en-us;q139444

> --if you have primary key defined in the table then you can have query as
> specified in below example
> --where filename and filepath are repeating

> create table [file](id int    ,FileName          varchar(500),FilePath
> varchar(500))
> insert into [file]
> select 1   ,'DailyReport','C:\...\dailyreport.rpt' union all
> select 2   ,'DailyReport','C:\...\dailyreport.rpt' union all
> select 3   ,'DailyReport','C:\...\dailyreportNew.rpt' union all
> select 4   ,'DailyReport','C:\...\dailyreportNew.rpt' union all
> select 5   ,'MonthlyReport','C:\...\monthlyreport.rpt' union all
> select 6   ,'YearlyReport','C:\...\yearlyreport.rpt'

> --query to keep one record out of all the duplicates assuming id as a
unique
> key.

> delete from [file]
> where id not in
> (select min(id) from [file] b where [file].filename=b.filename and
> [file].filepath=b.filepath )

> - Vishal


> >     I'm importing data from an outside source that does contain some
dupes
> > and I need to eliminate them. What structure should I use? Should I use
an
> > ID column and insert the dupes, then get rid of them. What is the best
> > method?
> > Thank You

> > Paul



Sun, 05 Mar 2006 12:02:33 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Dupe values vs. nulls in join results

2. SQL Query to insert w/out dupes

3. need SP to insert new key to one table and dupes to another

4. Removing Dupes

5. dupe checking

6. Faster insert with ignore dupe key

7. Remove all dupes but ...

8. delete dupe records

9. DUPES

10. Finding Similar Dupes in database

11. Dupes

12. how do I set no dupes allowed?


 
Powered by phpBB® Forum Software