Check if a row exists, otherwise insert

I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn’t exist, insert it. All this steps wrapped by a transaction.

This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was committed and the flight booked.

  • Higher Query result with the DISTINCT Keyword?
  • MSSQL query in C#, Geting part of WHERE clause from a string
  • Cannot create index on view 'View_Table_Name' because the view is not schema bound
  • SQL Server: enable remote connections without SSMS
  • database Project not running Post Deployment scripts
  • How to remove special characters from a string completely in sql server
  • I’m new to T-SQL, and not sure on how to use @@rowcount. This is what I’ve written until now. Am I on the right road? I’m sure is an easy problem for you.

    UPDATE Bookings
     SET TicketsBooked = TicketsBooked + @TicketsToBook
     WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)
    -- Here I need to insert only if the row doesn't exists.
    -- If the row exists but the condition TicketsMax is violated, I must not insert 
    -- the row and return FALSE
    IF @@ROWCOUNT = 0 
     INSERT INTO Bookings ... (omitted)
    -- Return TRUE (How to do?)

    10 Solutions collect form web for “Check if a row exists, otherwise insert”

    Take a look at MERGE command. You can do UPDATE, INSERT & DELETE in one statement.

    Here is a working implementation on using MERGE
    – It checks whether flight is full before doing an update, else does an insert.

    if exists(select 1 from INFORMATION_SCHEMA.TABLES T 
                  where T.TABLE_NAME = 'Bookings') 
        drop table Bookings
    create table Bookings(
      FlightID    int identity(1, 1) primary key,
      TicketsMax    int not null,
      TicketsBooked int not null
    insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
    insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
    insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
    select * from Bookings

    And then …

    declare @FlightID int = 1
    declare @TicketsToBook int = 2
    --; This should add a new record
    merge Bookings as T
    using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
        on  T.FlightID = S.FlightID
          and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
      when matched then
        update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
      when not matched then
        insert (TicketsMax, TicketsBooked) 
        values(S.TicketsToBook, S.TicketsToBook);
    select * from Bookings

    I assume a single row for each flight? If so:

    IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
        --UPDATE HERE
       -- INSERT HERE

    I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are 10 tickets max and you are booking 20.

    Pass updlock, rowlock, holdlock hints when testing for existence of the row.

    begin tran /* default read committed isolation level is fine */
    if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
        /* insert */
        /* update */
    commit /* locks are released here */

    The updlock hint forces the query to take an update lock on the row if it already exists, preventing other transactions from modifying it until you commit or roll back.

    The holdlock hint forces the query to take a range lock, preventing other transactions from adding a row matching your filter criteria until you commit or roll back.

    The rowlock hint forces lock granularity to row level instead of the default page level, so your transaction won’t block other transactions trying to update unrelated rows in the same page (but be aware of the trade-off between reduced contention and the increase in locking overhead – you should avoid taking large numbers of row-level locks in a single transaction).

    See for more information.

    Note that locks are taken as the statements which take them are executed – invoking begin tran doesn’t give you immunity against another transaction pinching locks on something before you get to it. You should try and factor your SQL to hold locks for the shortest possible time by committing the transaction as soon as possible (acquire late, release early).

    Note that row-level locks may be less effective if your PK is a bigint, as the internal hashing on SQL Server is degenerate for 64-bit values (different key values may hash to the same lock id).

    i’m writing my solution. my method doesn’t stand ‘if’ or ‘merge’. my method is easy.

    INSERT INTO TableName (col1,col2)
    SELECT @par1, @par2
       WHERE NOT EXISTS (SELECT col1,col2 FROM TableName
                         WHERE col1=@par1 AND col2=@par2)

    For Example:

    INSERT INTO Members (username)
    SELECT 'Cem'
       WHERE NOT EXISTS (SELECT username FROM Members
                         WHERE username='Cem')


    (1) SELECT col1,col2 FROM TableName WHERE col1=@par1 AND col2=@par2
    It selects from TableName searched values

    (2) SELECT @par1, @par2 WHERE NOT EXISTS
    It takes if not exists from (1) subquery

    (3) Inserts into TableName (2) step values

    This is something I just recently had to do:

    ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
          @CustomerID AS INT,
          @UserName AS VARCHAR(25),
          @Password AS BINARY(16)
            IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
                INSERT INTO [tblOnline_CustomerAccount] (
                ) VALUES ( 
                    /* CustomerID - int */ @CustomerID,
                    /* UserName - varchar(25) */ @UserName,
                    /* Password - binary(16) */ @Password,
                    /* LastLogin - datetime */ NULL ) 
                UPDATE  [tblOnline_CustomerAccount]
                SET     UserName = @UserName,
                        Password = @Password
                WHERE   CustomerID = @CustomerID    

    You could use the Merge Functionality to achieve. Otherwise you can do:

    declare @rowCount int
    select @rowCount=@@RowCount
    if @rowCount=0

    Full solution is below (including cursor structure). Many thanks to Cassius Porcus for the begin trans ... commit code from posting above.

    declare @mystat6 bigint
    declare @mystat6p varchar(50)
    declare @mystat6b bigint
    DECLARE mycur1 CURSOR for
     select result1,picture,bittot from  all_Tempnogos2results11
     OPEN mycur1
     FETCH NEXT FROM mycur1 INTO @mystat6, @mystat6p , @mystat6b
     WHILE @@Fetch_Status = 0
     begin tran /* default read committed isolation level is fine */
     if not exists (select * from all_Tempnogos2results11_uniq with (updlock, rowlock, holdlock)
                         where all_Tempnogos2results11_uniq.result1 = @mystat6 
                            and all_Tempnogos2results11_uniq.bittot = @mystat6b )
         insert all_Tempnogos2results11_uniq values (@mystat6 , @mystat6p , @mystat6b)
     --  /* update */
     commit /* locks are released here */
     FETCH NEXT FROM mycur1 INTO @mystat6 , @mystat6p , @mystat6b
     CLOSE mycur1
     DEALLOCATE mycur1

    I finally was able to insert a row, on the condition that it didn’t already exist, using the following model:

    INSERT INTO table ( column1, column2, column3 )
        SELECT $column1, $column2, $column3
          WHERE NOT EXISTS (
            SELECT 1
              FROM table 
              WHERE column1 = $column1
              AND column2 = $column2
              AND column3 = $column3 

    which I found at:

    INSERT INTO Database.dbo.Table SELECT * FROM Database.dbo.Table
     WHERE ID not in (select ID from Database.dbo.Table)'
    INSERT INTO table ( column1, column2, column3 )
    SELECT $column1, $column2, $column3
    EXCEPT SELECT column1, column2, column3
    FROM table
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.