CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Robert
Robert

Reputation:

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.

I'm sure on how to use @@rowcount. This is what I've written until now. Am I on the right road?

-- 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?)

Upvotes: 273

Views: 631628

Answers (12)

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

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.

Upvotes: 203

&#201;ric F
&#201;ric F

Reputation: 63

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)

Upvotes: 0

Almamun
Almamun

Reputation: 11

INSERT INTO [DatabaseName1].dbo.[TableName1] SELECT * FROM [DatabaseName2].dbo.[TableName2]
 WHERE [YourPK] not in (select [YourPK] from [DatabaseName1].dbo.[TableName1])

Upvotes: 1

Maurice Elagu
Maurice Elagu

Reputation: 704

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.

Upvotes: -2

Paul G
Paul G

Reputation: 847

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:

http://www.postgresql.org/message-id/[email protected]

Upvotes: 9

Aaron
Aaron

Reputation: 11

INSERT INTO table ( column1, column2, column3 )
SELECT $column1, $column2, $column3
EXCEPT SELECT column1, column2, column3
FROM table

Upvotes: -2

dance2die
dance2die

Reputation: 36975

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

Upvotes: 173

user2836818
user2836818

Reputation: 27

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

 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)

 --else
 --  /* update */

 commit /* locks are released here */

 FETCH NEXT FROM mycur1 INTO @mystat6 , @mystat6p , @mystat6b

 END

 CLOSE mycur1

 DEALLOCATE mycur1
 go

Upvotes: 1

Cem
Cem

Reputation: 856

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')

Explanation:

(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

Upvotes: 50

JoshBerke
JoshBerke

Reputation: 67118

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

declare @rowCount int

select @rowCount=@@RowCount

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

Upvotes: 1

Cassius Porcus
Cassius Porcus

Reputation: 821

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 */
else
    /* 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 http://msdn.microsoft.com/en-us/library/ms187373.aspx 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).

Upvotes: 74

TheTXI
TheTXI

Reputation: 37905

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

Upvotes: 3

Related Questions