Insert into ignore duplicates that are not the pri

问题:

I'm trying to find a query for sql that will just insert values but not do it should the value exist. Now ive seen alot of examples but they all rely on primary keys or table to table moves. I just want to add a new row in the table and assuming that one of the collumns doesnt have the same value add it. I know the following wont work but its as close to I think it would be and might just clear it up if my writting is not enough.

    INSERT INTO table (txtLastName,txtEmail,txtZip)
    Values ('Tester','[e-mail]','12345')
    WHERE txtLastName <> 'Tester'
    or WHERE txtEmail <> '[e-mail]'
    or WHERE txtZip <> '12345'

Using MS SQL Server.

回答1:

You should create a Unique Constraint composed by the three fields (txtLastName, txtEmail, txtZip).

The links directs you to SQL Server docs, but the concept of unique constraint is RDBMS universal.

Just beware that when you create a Unique Constraint, your duplicate insert will not just fail silently: it will throw an error saying the insert tried to violate the unique constraint. And, of course, it should do that! Make sure your code handles that exception.

回答2:

Try this:

INSERT INTO table (txtLastName,txtEmail,txtZip) 
SELECT 'Tester','[e-mail]','12345'
WHERE NOT EXISTS (SELECT *
                    FROM table
                   WHERE txtLastName = 'Tester' 
                     AND txtEmail = '[e-mail]' 
                     AND txtZip = '12345' 
)