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.

Nearly identical to this question: http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server — Merlyn Morgan-Graham, Nov 06 '11 at 21:31
@MerlynMorgan-Graham: There are many questions on SO asking how to do an UPSERT. The linked question is not nearly identical because it does not involve returning the ID of the row. — Charles Burns, Nov 06 '11 at 22:01
Same skeleton concept - if it exists, do one op. If it doesn't exist, do another op. Needs to be unique, so you can't just insert and call it a day. I didn't vote it as a dupe because it wasn't identical, just thought it might be helpful info. — Merlyn Morgan-Graham, Nov 06 '11 at 22:22

Hogan · Accepted Answer · 2017-03-08T14:56:22.883

19

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

edited Mar 08 '17 at 14:56

answered Nov 06 '11 at 21:18

Hogan

69,564
10
76
117

I marked this as the correct answer, but I am interested in your thoughts on the final query used below. – Charles Burns Nov 08 '11 at 03:40
@Charles - I think audioformat is small so it won't matter much, but your point is good. – Hogan Nov 08 '11 at 10:34
The second one doesn't work for me ('Incorrect syntax near WHERE') - should the VALUES actually be a SELECT? – jwg Mar 14 '13 at 14:37
1

@jwg - the second one was missing a `FROM` clause. – Hogan Mar 08 '17 at 14:57
if i am triggering the same from the sql server, i am unable to get the output (@audioFormatID) – Adithya Sai Feb 20 '18 at 09:05
@adithyasai -- do you have audioFormatID set as an output parameter? – Hogan Feb 21 '18 at 20:16
Did you try it with FROM inserted? – Yola Jan 31 '21 at 14:25
@Yola - is that a question for me? – Hogan Feb 12 '21 at 14:58
Yes, I think you broke it with your last edit. For me it work w/o `FROM` and doesn't with `FROM`. – Yola Feb 12 '21 at 15:07
are you using sql server 2008 @yola? – Hogan Feb 12 '21 at 17:47
It is on another PC but I believe it is either 2018 or 2019. I will check this query again tomorrow on that PC. – Yola Feb 12 '21 at 18:47
An equivalent for MySQL? – Meloman Jan 16 '23 at 09:10
Why wouldn't this work in MySQL @Meloman? – Hogan Jan 17 '23 at 19:31
"IF NOT EXISTS" for example doesn't exist in MySQL syntax. But I found a workaround. – Meloman Jan 18 '23 at 14:38
@Meloman why do you have the IF? It is in the docs right here https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html – Hogan Jan 18 '23 at 16:20
I finally used another way to do it. thanks – Meloman Jan 25 '23 at 08:36

Charles Burns · Answer 2 · 2011-11-12T21:09:51.737

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.

This is exactly the same code I'm using now, but I've found issues when under load where multiple sprocs might be running similar code at the same time. What happens is that the first IF statement returns NULL, but before the INSERT statement happens, another sproc has inserted the value. I then get an error about duplicate values. — Brian Vallelunga, Nov 17 '19 at 14:37
@BrianVallelunga Ideally this would be taking place in a transaction — ijustlovemath, Jun 25 '23 at 12:57

score 4 · Answer 3 · edited Jan 18 '21 at 12:30

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

+1 for link to StackOverflow query tester. I've read some SO answers to similar questions involving MERGE, but doesn't that involve T-SQL or otherwise something outside of the scope of a pure SQL query? — Charles Burns, Nov 06 '11 at 22:07
@Charles - Not quite sure what you mean by 'pure SQL query'? What constraints are you seeking on your sql? — Neil Moss, Nov 06 '11 at 22:09
Anything I can put into a .NET cmd.ExecuteNonQuery() statement (which may include your answer) which does not involve a stored procedure or other modification to the database structure aside from insertions. — Charles Burns, Nov 06 '11 at 22:17
@Charles - I see. In that case, I believe the query meets your requirements. So long as the relevant permissions exist to select from, insert to and update your table, then nothing else is required. — Neil Moss, Nov 06 '11 at 22:58

sll · Answer 4 · 2011-11-06T21:26:19.483

2

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

SELECT MAX(ID) FROM audioFormats

EDIT:

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.

edited Nov 06 '11 at 21:26

answered Nov 06 '11 at 21:06

sll

61,540
22
104
156

3

This is fine assuming that there is only ever one query executed by the application(s) at any given time. If not, you may end up returning the incorrect ID. – PostMan Nov 06 '11 at 21:08
Right, for multiple sessions case `IDENT_CURRENT('audioFormats')` could be heplful – sll Nov 06 '11 at 21:12
This may work, though I said in the question that it would be nice to avoid another query just to get the id. It seems like it should be possible to test the existence of a value and get the values ID all in one shot, and then do an insert only if said value does not exist. That would be a single INSERT and a single SELECT. Unfortunately, most of my experience is with Oracle, so even SCOPE_IDENTITY was new to me. – Charles Burns Nov 06 '11 at 22:04

Merlyn Morgan-Graham · Answer 5 · 2011-11-06T21:30:21.553

2

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.

edited Nov 06 '11 at 21:30

answered Nov 06 '11 at 21:24

Merlyn Morgan-Graham

58,163
16
128
183

audioformat and videoFormat are indeed set up with UNIQUE constraints. Thank you for mentioning this -- I should have stated this in my question. – Charles Burns Nov 06 '11 at 22:12
If the table has an identity column then every single no-insert-"lookup" will burn one identity number. – springy76 Jul 19 '18 at 09:09

INSERT IF NOT EXISTS but return the identity either way

5 Answers5

Linked