SQL - INSERT INTO only when the record does not exist

Question

I realize there a lot of answered SO question on this same topic, but I can't seem to use any of that information to figure out what's wrong with my query.

I'm trying to insert a record into my table only if a record with the same database_owner and database_name (those are two of my column names) does not already exist.

I'm using the query:

INSERT INTO users_databases (database_name, database_key, database_secret, database_owner) 
VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101-21f39e6ac8d9', 'x@x.com') 
WHERE
   NOT EXISTS 
   (SELECT database_name FROM uses_databases WHERE database_name = 'DB1' AND database_owner = 'x@x.com');

But I'm getting the error Incorrect syntax near the keyword 'WHERE'.

I should also point out that running the query

SELECT database_name 
FROM uses_databases 
WHERE database_name = 'DB1' AND database_owner = 'x@x.com'

does return a record.

Where could I be going wrong? From what I've read in the other questions, this looks correct. But it's obviously not. :-) Any advice would be super helpful.

possible duplicate of [INSERT INTO if not exists SQL server](http://stackoverflow.com/questions/9648934/insert-into-if-not-exists-sql-server) http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice — Woot4Moo, Sep 08 '13 at 22:49
If you use a WHERE clause, then you must use SELECT instead of VALUES. — Michael Liu, Sep 08 '13 at 23:14
Have a look at http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005 for discussion as to why all the answers so far are wrong. If this is sql 2008, look at the `merge` statement. — Laurence, Sep 08 '13 at 23:38
@Brett, please mark the post that helped you as the answer. Thanks! — dcaswell, Sep 09 '13 at 04:41
This is being marked as a duplicate, but the answer at http://stackoverflow.com/questions/9648934/insert-into-if-not-exists-sql-server is much more complicated than the answer given here. This answer here wins hands down. — Brett, Sep 09 '13 at 12:48

score 18 · Accepted Answer · answered Sep 08 '13 at 22:49

18

The easiest solution would be something like this:

IF NOT EXISTS (SELECT database_name FROM uses_databases WHERE database_name = 'DB1' AND database_owner = 'x@x.com')
    INSERT INTO users_databases (database_name, database_key, database_secret, database_owner) 
    VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101-21f39e6ac8d9', 'x@x.com');

answered Sep 08 '13 at 22:49

p.s.w.g

136,020
27
262
299

This did it! I guess I was trying the inverse. Thanks! – Brett Sep 08 '13 at 22:51
1

A good article to read on the fastest method for doing this [can be found here](http://cc.davelozinski.com/sql/fastest-way-to-insert-new-records-where-one-doesnt-already-exist) if you're interested in speed or have several million records affected. – Jul 24 '15 at 00:44

score 2 · Answer 2 · answered Sep 09 '13 at 00:41

You can do the following, must work

  INSERT INTO users_databases (database_name, database_key, database_secret,    database_owner) 
  VALUES ('DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', '367123d8-e5a7-46a0-8101- 21f39e6ac8d9', 'x@x.com') 
  WHERE 1
        NOT IN 
       (SELECT 1 FROM uses_databases WHERE database_name = 'DB1' AND database_owner = 'x@x.com');

score 1 · Answer 3 · answered Sep 08 '13 at 22:54

You can also do

INSERT INTO users_databases (database_name, database_key, database_secret, database_owner) 
SELECT 'DB1', '263f690d-7ac3-49f2-aa3b-f5672e4639a2', 
 '367123d8-e5a7-46a0-8101-21f39e6ac8d9', 'x@x.com'
 WHERE
   NOT EXISTS 
   (SELECT database_name 
    FROM uses_databases 
     WHERE database_name = 'DB1' AND database_owner = 'x@x.com'
    )

SQL - INSERT INTO only when the record does not exist

3 Answers3