IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

Question

How do you say the following in Microsoft SQL Server 2005:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN
   SELECT TableID FROM Table WHERE FieldValue=''
ELSE
   INSERT INTO TABLE(FieldValue) VALUES('')
   SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END IF

What I'm trying to do is to see if there is a blank fieldvalue already, and if there is then return that TableID, else insert a blank fieldvalue and return the corresponding primary key.

What programming language are you using? This might be better done there inside a transaction. — Byron Whitlock, Sep 28 '09 at 17:40
possible duplicate of [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Sep 30 '12 at 16:10
This is the older question; the 'Only inserting a row' question should be closed as a duplicate of this, not the other way round. — Jonathan Leffler, Oct 02 '12 at 08:54

score 69 · Answer 1 · edited May 20 '15 at 08:02

You need to do this in transaction to ensure two simultaneous clients won't insert same fieldValue twice:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
    DECLARE @id AS INT
    SELECT @id = tableId FROM table WHERE fieldValue=@newValue
    IF @id IS NULL
    BEGIN
       INSERT INTO table (fieldValue) VALUES (@newValue)
       SELECT @id = SCOPE_IDENTITY()
    END
    SELECT @id
COMMIT TRANSACTION

you can also use Double-checked locking to reduce locking overhead

DECLARE @id AS INT
SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
        SELECT @id = tableID FROM table WHERE fieldValue=@newValue
        IF @id IS NULL
        BEGIN
           INSERT INTO table (fieldValue) VALUES (@newValue)
           SELECT @id = SCOPE_IDENTITY()
        END
    COMMIT TRANSACTION
END
SELECT @id

As for why ISOLATION LEVEL SERIALIZABLE is necessary, when you are inside a serializable transaction, the first SELECT that hits the table creates a range lock covering the place where the record should be, so nobody else can insert the same record until this transaction ends.

Without ISOLATION LEVEL SERIALIZABLE, the default isolation level (READ COMMITTED) would not lock the table at read time, so between SELECT and UPDATE, somebody would still be able to insert. Transactions with READ COMMITTED isolation level do not cause SELECT to lock. Transactions with REPEATABLE READS lock the record (if found) but not the gap.

+1 I don't understand why the **only** answer that considers race conditions and concurrency is languishing on zero votes. — Martin Smith, Sep 30 '12 at 16:07
May you expand to explain why `ISOLATION LEVEL SERIALIZABLE` is is necessary and what can happen if you don’t set that? — binki, Sep 09 '13 at 17:18
@binki, when inside a serializable transaction, the first SELECT that hits the table, creates a *range lock* covering the place where the record should be, so nobody else can insert the same record, until this transaction ends. Without ISOLATION LEVEL SERIALIZABLE, the default isolation level (READ COMMITTED) would not lock the table at read time, so between select and update, somebody would still be able to insert. Transactions with READ COMMITTED isolation level, do not cause SELECT to lock. Transactions with REPEATABLE READS, lock the record (if found) but not the gap. — Andriy Volkov, Feb 24 '14 at 22:19

score 37 · Answer 2 · answered Sep 28 '09 at 17:44

37

IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='') 
BEGIN
   SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
   INSERT INTO TABLE(FieldValue) VALUES('')
   SELECT SCOPE_IDENTITY() AS TableID
END

See here for more information on IF ELSE

Note: written without a SQL Server install handy to double check this but I think it is correct

Also, I've changed the EXISTS bit to do SELECT 1 rather than SELECT * as you don't care what is returned within an EXISTS, as long as something is I've also changed the SCOPE_IDENTITY() bit to return just the identity assuming that TableID is the identity column

answered Sep 28 '09 at 17:44

Jane

1,953
1
20
27

2

The 'SELECT 1' doesn't matter. Are you changing it just to point out that you don't care about the details? It doesn't help performance. – Rob Garrison Sep 28 '09 at 17:51
4

I prefer to avoid SELECT * amongst my code - it doesn't feel like a good habit to get into, so I usually do a SELECT 1 when doing an exists – Jane Sep 29 '09 at 20:13
The solution by @zvolkov is slightly better than this as it uses a variable and only has to `SELECT FROM Table` once rather than twice. – Neo Nov 13 '13 at 12:39

score 7 · Answer 3 · answered Sep 28 '09 at 17:41

7

You were close:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='')
   SELECT TableID FROM Table WHERE FieldValue=''
ELSE
BEGIN
   INSERT INTO TABLE (FieldValue) VALUES ('')
   SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END

answered Sep 28 '09 at 17:41

David

34,223
3
62
80

score 2 · Answer 4 · answered Sep 28 '09 at 17:45

You just have to change the structure of the if...else..endif somewhat:

if exists(select * from Table where FieldValue='') then begin
  select TableID from Table where FieldValue=''
end else begin
  insert into Table (FieldValue) values ('')
  select TableID from Table where TableID = scope_identity()
end

You could also do:

if not exists(select * from Table where FieldValue='') then begin
  insert into Table (FieldValue) values ('')
end
select TableID from Table where FieldValue=''

Or:

if exists(select * from Table where FieldValue='') then begin
  select TableID from Table where FieldValue=''
end else begin
  insert into Table (FieldValue) values ('')
  select scope_identity() as TableID
end

score 2 · Answer 5 · answered Sep 29 '09 at 08:35

It sounds like your table has no key. You should be able to simply try the INSERT: if it’s a duplicate then the key constraint will bite and the INSERT will fail. No worries: you just need to ensure the application doesn't see/ignores the error. When you say 'primary key' you presumably mean IDENTITY value. That's all very well but you also need a key constraint (e.g. UNIQUE) on your natural key.

Also, I wonder whether your procedure is doing too much. Consider having separate procedures for 'create' and 'read' actions respectively.

Rob Garrison · Answer 6 · 2009-09-28T19:01:31.873

DECLARE @t1 TABLE (
    TableID     int         IDENTITY,
    FieldValue  varchar(20)
)

--<< No empty string
IF EXISTS (
    SELECT *
    FROM @t1
    WHERE FieldValue = ''
) BEGIN
    SELECT TableID
    FROM @t1
    WHERE FieldValue=''
END
ELSE BEGIN
    INSERT INTO @t1 (FieldValue) VALUES ('')
    SELECT SCOPE_IDENTITY() AS TableID
END

--<< A record with an empty string already exists
IF EXISTS (
    SELECT *
    FROM @t1
    WHERE FieldValue = ''
) BEGIN
    SELECT TableID
    FROM @t1
    WHERE FieldValue=''
END
ELSE BEGIN
    INSERT INTO @t1 (FieldValue) VALUES ('')
    SELECT SCOPE_IDENTITY() AS TableID
END

score 1 · Answer 7 · answered Jan 02 '17 at 18:00

create schema tableName authorization dbo
go
IF OBJECT_ID ('tableName.put_fieldValue', 'P' ) IS NOT NULL 
drop proc tableName.put_fieldValue
go
create proc tableName.put_fieldValue(@fieldValue int) as
declare @tableid int = 0
select @tableid = tableid from table where fieldValue=''
if @tableid = 0 begin
   insert into table(fieldValue) values('')
   select @tableid = scope_identity()
end
return @tableid
go
declare @tablid int = 0
exec @tableid = tableName.put_fieldValue('')

IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

7 Answers7

Linked