How To Insert A Row Only If It's Not Present

Hi,

I've been asked to find a way to insert a row only if it's not present. I want to make sure that test and insert are atomic, so I wrote the following test cases


-- CREATE TABLE t1 ( id int, val varchar(256) )

-- GO


-- Execute the following statements in one query window

IF NOT EXISTS(SELECT * FROM t3 WHERE id = 1)

BEGIN


WAITFOR DELAY '00:00:10'

INSERT INTO t3(id, val) VALUES (1, 'Query 1')

END

GO


-- Then execute the following statements in a second query window within ten

-- seconds of the starting the first query

IF NOT EXISTS(SELECT * FROM t3 WHERE id = 1)

BEGIN


WAITFOR DELAY '00:00:10'

INSERT INTO t3(id, val) VALUES (1, 'Query 2')

END

GO


When both queries are complete there will be two rows in the table t with id = 1.

1 Query 2
1 Query 1

Wrapping the code in a transaction does not change the result. Changing the transaction isolation level to serialable causes a deadlock, but otherwise the isolation level has no effect on the result either. Adding a WITH (TABLOCK) hint to the tests do not change the result.

Our clients use a mix of SQL 2000 and SQL 2005 so TRY/CATCH blocks cannot be used.

Any suggestion on how to do this?

Thanks