INSERT IF NOT EXISTS but return the identity either way

Question

I have 3 tables: audioFormats, videoFormats, and fileInfo. I have a transaction such that when I insert into the fileInfo table, that insert includes an FK from audioFormats and videoFormats. An insertion into the latter tables takes place if the audio format or video format are not already in those tables, then the generated (or existing) ID value is inserted into fileInfo.

How do I efficiently insert a value only if that value does not exist, but get the ID of the value whether it already exists or was freshly inserted using only SQL (and perhaps a transaction).

I can insert a value if it does not already exist:

INSERT INTO audioformats (audioformat) VALUES(@format) WHERE NOT EXISTS (SELECT 1 FROM audioformats WHERE audioformat = @format) 

I can get the inserted ID from an insertion:

INSERT INTO audioFormats (audioFormat) VALUES ('Test') SET @audioFormatId = SCOPE_IDENTITY() 

SCOPE_IDENTITY won't give me an ID value if no insertion took place. I can execute a scalar query to get the identity after a possible insertion, but it seems like I should be able to do all of this with at most one SELECT and INSERT.

Answer 1

You can use an IF statement to do this

IF NOT EXISTS(SELECT TOP 1 1 FROM audioformats WHERE audioformat = @format) BEGIN   INSERT INTO audioFormats (audioFormat)   VALUES ('Test')   SET @audioFormatId = SCOPE_IDENTITY() END ELSE BEGIN   SELECT @audioFormatID = ID FROM audioformats WHERE audioformat = @format END 

or you could do it like this:

INSERT INTO audioformats (audioformat)   SELECT @format   FROM audioFormats   WHERE NOT EXISTS (SELECT TOP 1 1 FROM audioformats WHERE audioformat = @format)  SELECT @audioFormatID = ID FROM audioformats WHERE audioformat = @format 

Answer 2

I marked an answer as correct because it was the most similar to my final SQL, but it did not really fulfill the original requirement of using at most one query and one insert. The following does, and is what I ended up using:

-- One select SELECT @audioFormatId = id FROM audioformats WHERE audioformat = @audioFormat;  -- Optionally one insert IF @audioFormatId IS NULL AND @audioFormat IS NOT NULL BEGIN     INSERT INTO audioFormats (audioFormat)     VALUES (@audioFormat)     SET @audioFormatId = SCOPE_IDENTITY() END 

Hogan's answer will probably be faster when most calls to the query do in fact perform an insertion because an IF NOT EXISTS query is faster than one that actually does return rows.

Mine will probably be faster in cases where the value already exists most of the time because then exactly one query (and no IF NOT EXISTS query) will be made. I suspect the null check is trivial.