SQL to search duplicates

Answer 1

To just check if a Lion is already in the table:

select count(*) from animals where name = 'Lion'

You can do the check and the insert in one query with a where clause:

insert into animals (name) 
select  'Lion'
where not exists 
        (
        select * from animals where name = 'Lion'
        )

In reply to your comment, to select a sub-list of animals:

select name from animals where name in ('Lion', 'Tiger', 'Jaguar')

This would return up to 3 rows for each animal that already exists.

Answer 2

SELECT COUNT(your_animal_column) FROM tblAnimals WHERE your_animal_column = ?;

The question marks get filled by your csv values. If this statement returns more than 0 there the value already exists

Answer 3

If your incoming file is already in a normalized column format, instead of comma separated like you have displayed, it should be easy. Create a temp table for the insert, then something like..

insert into YourLiveTable ( animalname ) 
   select Tmp.animalname
      from YourTempInsertTable Tmp
      where Tmp.animalname not in 
               ( select Live.animalname  
                    from YourLiveTable Live )

To match your revised request... just use the select portion and change "NOT IN" to "IN"

   select Tmp.animalname
      from YourTempInsertTable Tmp
      where Tmp.animalname IN 
               ( select Live.animalname  
                    from YourLiveTable Live )

Answer 4

How about

SELECT ANIMAL_NAME, COUNT(*)
FROM ANIMALS
GROUP BY ANIMAL_NAME
HAVING COUNT(*) > 1

This should give you all the duplicate entries