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)
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.
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
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.
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. merge @tblAudioFormats as Target using (select @AudioFormat as AudioFormat) as source(AudioFormat) on (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
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.
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.