INSERT IF NOT EXISTS but return the identity either way

INSERT IF NOT EXISTS but return the identity either way

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)
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.


Solution 1:

You can use an IF statement to do this

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

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

Solution 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
    INSERT INTO audioFormats (audioFormat)
    VALUES (@audioFormat)
    SET @audioFormatId = SCOPE_IDENTITY()

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.

Solution 3:

I think you can use MERGE for this.

This gives you a transactionless solution. Some bright spark might be able to improve this using the output clause to get the target id, but below will work just fine.

I’ve added a link to the StackOverflow query tester below.

-- Merge example
-- Get ID of existing row or insert new row

-- Initialise unit test data
declare @AudioFormatId int;
declare @AudioFormat nvarchar(50)
declare @tblAudioFormats TABLE (AudioFormatId int identity, AudioFormat nvarchar(50)   );
insert into @tblAudioFormats(AudioFormat) values ('MP3'), ('WAV');

-- set query criteria
set @AudioFormat = 'MP3' -- query below returns 1 - updating MP3
--set @AudioFormat = 'WAV' -- query below returns 2 - updating WAV
--set @AudioFormat = 'MIDI' -- query below returns 3 - inserting MIDI

-- Insert or update AudioFormat and return id of target audio format.
  @tblAudioFormats as Target
  (select @AudioFormat as AudioFormat) as source(AudioFormat)
  (source.AudioFormat = target.AudioFormat)
when matched then
  update set @AudioFormatID = target.AudioFormatId
when not matched then
  insert(AudioFormat) values (source.AudioFormat);

if @AudioFormatId is null set @AudioFormatId = scope_identity()

-- return ID of target audio format
select @AudioFormatId as TargetAudioFormatId

Run this query here: Query StackOverflow link for sample

Solution 4:

If audioFormats table has autoincrement IDENTITY(1,1) PK you can get just inserted ID by simple select:

SELECT MAX(ID) FROM audioFormats 


As was mentioned in comment this approach is applicable when only one query inserting into a table.

Otherwise you can take a look at the IDENT_CURRENT(‘table_name’) function.

IDENT_CURRENT Returns the last identity value generated for a
specified table or view. The last identity value generated can be for
any session and any scope.

Solution 5:

You’ll need to guarantee your audioformat column is unique via a UNIQUE constraint, and put your code in a try/catch.

Try to insert. If it fails, then catch, query for the new entry, and return the existing ID.

You could try querying first. But if someone inserts between the start of your batch and when you insert, the DB will throw an exception anyhow.