SQLite: Alternative to IF NOT EXISTS

SQLite does not natively support the IF NOT EXISTS syntax, which is commonly used in Microsoft SQL Server to conditionally insert data into a table only if it does not already exist. However, there are several alternative approaches to achieve the same functionality in SQLite.

1. INSERT OR IGNORE

The INSERT OR IGNORE statement instructs SQLite to attempt an insertion into the specified table, ignoring any errors that would result from duplicate key violations. This effectively creates a new row if the specified key does not exist and does nothing if it already exists.

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

2. SELECT...WHERE NOT EXISTS

This alternative approach uses a SELECT statement to check for the existence of the record before performing the insertion. If the SELECT statement returns no rows (indicating the record does not exist), the INSERT statement is executed.

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

Both of these approaches provide alternatives to the IF NOT EXISTS syntax and can be used to conditionally insert data into a SQLite table only if the specified key does not exist.