Check if a row exists otherwise insert

Question

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  lt   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

User · Answer

Take a look at MERGE command  You can do UPDATE  INSERT  amp  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  gt   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 · Answer

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

User · Answer

INSERT INTO  DatabaseName1  dbo  TableName1  SELECT   FROM  DatabaseName2  dbo  TableName2   WHERE  YourPK  not in  select  YourPK  from  DatabaseName1  dbo  TableName1

User · Answer

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 · Answer

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

User · Answer

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 87hdow4ld1 fsf stark xeocode com

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You could use the Merge Functionality to achieve  Otherwise you can do   declare  rowCount int  select  rowCount   RowCount  if  rowCount 0 begin --insert

User · Answer

INSERT INTO table   column1  column2  column3   SELECT  column1   column2   column3 EXCEPT SELECT column1  column2  column3 FROM table

[sql] Check if a row exists, otherwise insert

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql