Sprawdź, czy istnieje wiersz, w przeciwnym razie wstaw

Muszę napisać procedurę składowaną T-SQL, która aktualizuje wiersz w tabeli. Jeśli wiersz nie istnieje, wstaw go. Wszystkie te kroki są zawinięte przez transakcję.

Dotyczy to systemu rezerwacji, więc musi być atomowy i niezawodny . Zwraca wartość true, jeśli transakcja została popełniona, a lot zarezerwowany.

Jestem nowym użytkownikiem T-SQL i nie jestem pewien, jak go używać @@rowcount. Tak pisałem do tej pory. Czy jestem na właściwej drodze? Jestem pewien, że to dla ciebie łatwy problem.

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

Spójrz na polecenie MERGE . Można to zrobić UPDATE, INSERTi DELETEw jednym sprawozdaniu.

Oto działająca implementacja używania MERGE
- Sprawdza, czy lot jest pełny przed wykonaniem aktualizacji, w przeciwnym razie wstawia.

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

I wtedy ...

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

Zakładam jeden rząd dla każdego lotu? W takim razie:

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

Zakładam to, co powiedziałem, ponieważ twój sposób robienia rzeczy może przepełnić rezerwację lotu, ponieważ wstawi nowy wiersz, gdy będzie maksymalnie 10 biletów, a ty rezerwujesz 20.

Przekaż updlock, rowlock, wskazówki blokady podczas testowania istnienia rzędu.

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 */

Wskazówka dotycząca aktualizacji zmusza zapytanie do zablokowania aktualizacji w wierszu, jeśli już istnieje, uniemożliwiając modyfikowanie go przez inne transakcje do momentu zatwierdzenia lub wycofania.

Wskazówka blokowania zmusza zapytanie do zablokowania zakresu, zapobiegając dodawaniu przez inne transakcje wiersza zgodnego z kryteriami filtrowania do momentu zatwierdzenia lub wycofania.

Wskazówka rowlock wymusza blokowanie ziarnistości do poziomu wiersza zamiast domyślnego poziomu strony, więc twoja transakcja nie blokuje innych transakcji próbujących zaktualizować niepowiązane wiersze na tej samej stronie (ale pamiętaj o kompromisie między zmniejszoną rywalizacją a wzrostem liczby blokowanie narzutów - należy unikać przyjmowania dużej liczby blokad na poziomie wiersza w jednej transakcji).

Więcej informacji można znaleźć na stronie http://msdn.microsoft.com/en-us/library/ms187373.aspx .

Zwróć uwagę, że blokady są pobierane, gdy wykonywane są instrukcje, które je pobierają - wywoływanie begin tran nie daje odporności na kolejną transakcję szczypania blokad na czymś, zanim dojdziesz do tego. Powinieneś spróbować uwzględnić czynnik SQL w celu utrzymywania blokad przez możliwie najkrótszy czas, zatwierdzając transakcję tak szybko, jak to możliwe (pozyskaj późno, zwolnij wcześniej).

Zauważ, że blokady na poziomie wierszy mogą być mniej skuteczne, jeśli twoja PK jest bigintem, ponieważ wewnętrzne haszowanie w SQL Server jest zdegenerowane dla wartości 64-bitowych (różne wartości kluczy mogą mieć skrót do tego samego identyfikatora blokady).

piszę moje rozwiązanie. moja metoda nie oznacza „jeśli” ani „scal”. moja metoda jest łatwa.

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

Na przykład:

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

Wyjaśnienie:

(1) WYBIERZ col1, col2 FROM TableName GDZIE col1 = @ par1 AND col2 = @ par2 Wybiera z przeszukiwanych wartości TableName

(2) WYBIERZ @ par1, @ par2 GDZIE NIE ISTNIEJE Pobiera, jeśli nie istnieje z (1) podzapytania

(3) Wstawia do wartości kroku TableName (2)

W końcu udało mi się wstawić wiersz, pod warunkiem, że jeszcze nie istniał, używając następującego modelu:

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

które znalazłem na:

http://www.postgresql.org/message-id/87hdow4ld1.fsf@stark.xeocode.com

To właśnie niedawno musiałem zrobić:

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

Aby to osiągnąć, możesz użyć funkcji scalania . W przeciwnym razie możesz zrobić:

declare @rowCount int

select @rowCount=@@RowCount

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

Pełne rozwiązanie znajduje się poniżej (w tym struktura kursora). Ogromne podziękowania dla Cassiusa Porcusa za begin trans ... commitkod zamieszczony powyżej.

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

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

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

Najlepszym podejściem do tego problemu jest najpierw uczynienie kolumny bazy danych UNIQUE

ALTER TABLE table_name ADD UNIQUE KEY

THEN INSERT IGNORE INTO table_name , wartość nie zostanie wstawiona, jeśli spowoduje zduplikowanie klucza / już istnieje w tabeli.