SQL Select or Insert return ID

Question

Ok, so a quick SQL question here (using sql-server-2008).

I have a names mapping table with the following columns

ID DisplayName

What I want to do first SELECT [ID] FROM [names] WHERE [DisplayName] = 'chuck';

BUT, if the name "chuck" does not exist in the database, I would like to create it and return an auto incremented ID .

I was wondering if SQL has some kind of built-in way to do this in a simple way or if I need to go a long route?

a long route will be something like this

 SELECT COUNT(ID) AS count, ID FROM names WHERE DisplayName='chuck' IF(count > 0) SELECT ID as ReturnID; ELSE BEGIN INSERT INTO names(DisplayName) values('chuck'); SELECT scope_identity() as ReturnID; END 

I have not tested the last statement, but I assume that the long road will be like this. If there is no built-in method, I would appreciate if someone could just fix this statement (since I'm sure this is not entirely correct).

Answer 1

You should also take care of transactions:

 set XACT_ABORT on begin tran declare @ID int select @ID = ID from names with (holdlock, updlock) WHERE DisplayName='chuck' if @@rowcount = 0 begin INSERT INTO names(DisplayName) values('chuck'); set @ID = scope_identity(); end select @ID as ReturnID; commit tran 

Pay attention to the use of table hints - holdlock and updlock . They prevent another thread from doing exactly the same request and creating a line a second time. For more information, find isolation, synchronization, deadlocks, simultaneous updates.

Answer 2

I would do:

 IF (NOT EXISTS(SELECT null from names where DisplayName='Chuck')) INSERT INTO Names (DisplayName) Values ('Chuck') SELECT ID as ReturnID FROM Names where DisplayName='Chuck' 

Not sparingly, but