Testing for existence of a row and insert it if not present in InterBase

Question

I am looking for a way to insert rows into an InterBase table if they do not already exist in the table. I cannot guarantee that the InterBase table is not empty to begin with.

In SQL Server, I could do something like the WHERE NOT EXISTS in the following script sample where the output is the same whether or not the commented line is uncommented or not. In SQL Server, the script continues to function even if the first INSERT INTO is also commented out.

CREATE TABLE #Table(A VARCHAR(3), B INT, C INT);
INSERT INTO #Table(A, B, C) VALUES ('a1', 1, 11);
--INSERT INTO #Table(A, B, C) VALUES ('a2', 2, 12);

INSERT INTO #Table(A, B, C)
    SELECT 'a2', 2, 12
    WHERE NOT EXISTS (
        SELECT 1
        FROM #Table WHERE A='a2' AND B=2 AND c=12)

SELECT * FROM #Table;

DROP TABLE #Table;

How would I do the equivalent of this in InterBase?

Note, there is a second question embedded in this script because I know "FROM" is required by syntax for a "SELECT" in InterBase and there is no "FROM" in the line "SELECT 'a2', 2, 12", and I am curious what the best way to handle that is from someone experienced with InterBase.

Answer 1

After I laid out the question like above, it turns out to be a whole lot easier than the paths my mind were traveling through. I was looking for a solution I could automate and this one looks like it would be pretty straight forward. A "LEFT JOIN" on the temporary table looks like it works and the method for obtaining one row is just to use a system table. Something like this:

INSERT INTO Tmp(A, B, C)
    SELECT 'a2', 2, 12
    FROM RDB$DATABASE LEFT JOIN
         Tmp ON Tmp.A='a2' AND Tmp.B=2 AND Tmp.C=12
    WHERE Tmp.B IS NULL
    ROWS 1;