Search code examples
sqlansi-sql-92

replacing TSQL NOT EXISTS in SQL-92


I am have following code which works well in TSQL:

BEGIN 
IF NOT EXISTS (select * from tblDCUSTOM where id = 'All Customers')
    BEGIN
    INSERT INTO tblDCUSTOM
        (ID
    ,Name
    ,English     
   )
   SELECT 'All Customers','All Customers','All Customers'
   END
END

Now, I need to have this functionality in an custom environment, where SQL-92 is used - so no EXISTS (edit: not true, EXISTS works in SQL-92) or BEGIN-END is possible. Any Ideas?


Solution

  • As per very first comment;

       INSERT INTO tblDCUSTOM
            (ID
        ,Name
        ,English     
       )
       SELECT 'All Customers','All Customers','All Customers'
       WHERE (SELECT COUNT(*) FROM tblDCUSTOM where id = 'All Customers') >= 1
    

    If TOP is supported this might be better

       INSERT INTO tblDCUSTOM
            (ID
        ,Name
        ,English     
       )
       SELECT 'All Customers','All Customers','All Customers'
       WHERE (SELECT TOP 1 1 as F FROM tblDCUSTOM where id = 'All Customers') IS NOT NULL
    

    I must warn you, many have tried to make a 'database agnostic' system. It's not worth it.