Need help catching duplicates from csv import

[siriiven]

I have a page where a user can import a csv file. I want to check for duplicates and either disregard them and only use the number once or delete the duplicates. Here is my code:

<cffile action="read" file="#form.FiletoUpload#" variable="csvfile">
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">


<cfquery name="massimport1" datasource="#bads#">
INSERT INTO table1(ID, RID, Level, Schedule, Date1,Date2)
VALUES ('#listgetAt('#index#',1, ',')#', '#id#', '#form.Level#', '#form.Schedule#', '#form.date1#', '#form.date2#')
</cfquery>

</cfloop>

Is there any way to eliminate duplicate ID numbers entered at that step? Any ideas would be appreciated.

[MichaelSJudd]

You could import to a temporary table, then do a SELECT DISTINCT on the fields that you are checking for dups before doing your insert.

That's what I do, anyway.

- Mike

[MichaelSJudd]

I forgot to mention - don't forget that impacts the rights they need in the datasource definition!

- Mike

[Dan_Bracuk]

use this type of insert query

insert into mytable
(fields)
select distinct values
from SomeSmallTable
where
(select count(*)
from mytable
where somefield = somevalue) = 0

[siriiven]

Thank you guys. This works great!