Check if a row exists, otherwise insert

Admin 1
Viewed 579877

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.

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.

-- BEGIN TRANSACTION (HOW TO DO?)

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 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)
sql-server-2008 tsql sql-server sql
12 Answers

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

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

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.

Gregory_A_Beamer_687250

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') 
begin
    drop table Bookings
end
GO

create table Bookings(
  FlightID    int identity(1, 1) primary key,
  TicketsMax    int not null,
  TicketsBooked int not null
)
GO

insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

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
user_40350

This is something I just recently had to do:

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

    END
TheTXI_221640

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

declare @rowCount int

select @rowCount=@@RowCount

if @rowCount=0
begin
--insert....
Josh_261600

Simple way to copy data from T1 to T2 and avoid duplicate in T2

--Insert a new record
INSERT INTO dbo.Table2(NoEtu, FirstName, LastName)
SELECT t1.NoEtuDos, t1.FName, t1.LName 
FROM dbo.Table1 as t1
WHERE NOT EXISTS (SELECT (1) FROM dbo.Table2  AS t2
                    WHERE t1.FName = t2.FirstName
                        AND t1.LName = t2.LastName
                        AND t1.NoEtuDos = t2.NoEtu)
user_21754490

The best approach to this problem is first making the database column UNIQUE

ALTER TABLE table_name ADD UNIQUE KEY

THEN INSERT IGNORE INTO table_name ,the value won't be inserted if it results in a duplicate key/already exists in the table.

user_28415700
Related