How to do IF NOT EXISTS in SQLite

Question

I am trying to port this line from MS SQL Server to SQLite

IF NOT EXISTS(SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received')      INSERT INTO EVENTTYPE (EventTypeName) VALUES ('ANI Received'); 

It seems that SQLite does not support IF NOT EXISTS or at least I can't make it work. Am I missing something simple? Is there a workaround?

Answer 1

How about this?

INSERT OR IGNORE INTO EVENTTYPE (EventTypeName) VALUES 'ANI Received' 

(Untested as I don't have SQLite... however this link is quite descriptive.)

Additionally, this should also work:

INSERT INTO EVENTTYPE (EventTypeName) SELECT 'ANI Received' WHERE NOT EXISTS (SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received'); 

Answer 2

If you want to ignore the insertion of existing value, there must be a Key field in your Table. Just create a table With Primary Key Field Like:

CREATE TABLE IF NOT EXISTS TblUsers (UserId INTEGER PRIMARY KEY, UserName varchar(100), ContactName varchar(100),Password varchar(100)); 

And Then Insert Or Replace / Insert Or Ignore Query on the Table Like:

INSERT OR REPLACE INTO TblUsers (UserId, UserName, ContactName ,Password) VALUES('1','UserName','ContactName','Password'); 

It Will Not Let it Re-Enter The Existing Primary key Value... This Is how you can Check Whether a Value exists in the table or not.