SQL to search duplicates

I have a table for animals like

• Lion
• Tiger
• Elephant
• Jaguar
• List item
• Cheetah
• Puma
• Rhino

I want to insert new animals in this table and I am t reading the animal names from a CSV file.

Suppose I got following names in the file 开发者_JAVA技巧

Lion,Tiger,Jaguar

as these animals are already in "Animals" table, What should be a single SQL query that will determine if the animals are already exist in the table.

Revision 1

I want a query that will give me the list of animals that are already in table. I donot want a query to insert that animal. I just want duplicate animals

---

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.

---

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

---

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 )

---

How about

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

This should give you all the duplicate entries