Wstaw SQL Server, jeśli nie istnieje


243

Chcę wstawić dane do mojej tabeli, ale wstaw tylko te dane, które jeszcze nie istnieją w mojej bazie danych.

Oto mój kod:

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

Błąd to:

Msg 156, poziom 15, stan 1, e-maile z procedurami RecebidosInsert, wiersz 11
Niepoprawna składnia w pobliżu słowa kluczowego „GDZIE”.


10
Nie powinieneś polegać wyłącznie na tym sprawdzeniu, aby upewnić się, że nie ma duplikatów, nie jest on bezpieczny dla wątków i otrzymasz duplikaty, gdy zostanie spełniony warunek wyścigu. Jeśli naprawdę potrzebujesz unikalnych danych, dodaj unikalne ograniczenie do tabeli, a następnie wychwyć błąd naruszenia ograniczenia. Zobacz tę odpowiedź
GarethD

1
Możesz użyć zapytania MERGE lub Jeśli nie istnieje (wybierz instrukcję) rozpocznij wstawianie wartości KONIEC
Abdul Hannan Ijaz

To zależy od scenariusza, czy powinieneś przekazać tę kontrolę, czy nie. Jeśli opracowujesz na przykład skrypt wdrażania, który zapisuje dane w tabeli „statycznej”, nie stanowi to problemu.
AxelWass,

możesz użyć „jeśli nie istnieje (wybierz * z ...”), takiego jak stackoverflow.com/a/43763687/2736742
A. Morel

2
@GarethD: co masz na myśli mówiąc „nie jest bezpieczny dla wątków”? Może nie jest elegancki, ale dla mnie wygląda poprawnie. Pojedynczy insertwyciąg jest zawsze pojedynczą transakcją. To nie tak, że SQL Server najpierw ocenia podkwerendę, a następnie w pewnym momencie i bez przytrzymywania blokady kontynuuje wstawianie.
Ed Avis,

Odpowiedzi:


322

zamiast poniżej kodu

BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   VALUES (@_DE, @_ASSUNTO, @_DATA)
   WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA);
END

zamienić

BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

Zaktualizowano: (dzięki @Marc Durdin za wskazanie)

Zauważ, że przy dużym obciążeniu czasami to się nie powiedzie, ponieważ drugie połączenie może przejść test IF NOT EXISTS, zanim pierwsze połączenie wykona INSERT, tj. Warunek wyścigu. Zobacz stackoverflow.com/a/3791506/1836776, aby uzyskać dobrą odpowiedź na pytanie, dlaczego nawet zawarcie transakcji nie rozwiązuje tego problemu.


20
Zauważ, że przy dużym obciążeniu czasami to się nie powiedzie, ponieważ drugie połączenie może przejść test IF NOT EXISTS, zanim pierwsze połączenie wykona INSERT, tj. Warunek wyścigu. Zobacz Patrz stackoverflow.com/a/3791506/1836776, aby uzyskać dobrą odpowiedź na pytanie, dlaczego nawet zawarcie transakcji nie rozwiązuje tego problemu.
Marc Durdin,

11
WYBIERZ 1 Z E-maili Recebidos GDZIE De = @_DE ORAZ Assunto = @_ASSUNTO ORAZ Dane = @_DANE Zastosowanie 1 zamiast * byłoby bardziej wydajne
Reno

1
Umieść blokadę zapisu wokół całej rzeczy, a wtedy nie będziesz mieć szans na duplikaty.
Kevin Finkenbinder

10
@jazzcat select *w tym przypadku nie robi żadnej różnicy, ponieważ jest używany w EXISTSklauzuli. SQL Server zawsze go optymalizuje i robi to od wieków. Ponieważ jestem bardzo stary, zwykle piszę te zapytania, EXISTS (SELECT 1 FROM...)ale nie są one już potrzebne.
Loudenvier,

16
Dlaczego tego rodzaju proste pytanie budzi więcej wątpliwości niż pewności?
drowa

77

Dla tych, którzy szukają najszybszego sposobu , ostatnio natknąłem się na te testy porównawcze, w których najwyraźniej użycie „INSERT SELECT ... EXCEPT SELECT ...” okazało się najszybsze dla 50 milionów rekordów lub więcej.

Oto przykładowy kod z artykułu (trzeci blok kodu był najszybszy):

INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
-----------------------------------
MERGE #table1 as [Target]
USING  (select Id, guidd, TimeAdded, ExtraData from #table2) as [Source]
(id, guidd, TimeAdded, ExtraData)
    on [Target].id =[Source].id
WHEN NOT MATCHED THEN
    INSERT (id, guidd, TimeAdded, ExtraData)
    VALUES ([Source].id, [Source].guidd, [Source].TimeAdded, [Source].ExtraData);
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT id, guidd, TimeAdded, ExtraData from #table2
EXCEPT
SELECT id, guidd, TimeAdded, ExtraData from #table1
------------------------------
INSERT INTO #table1 (id, guidd, TimeAdded, ExtraData)
SELECT #table2.id, #table2.guidd, #table2.TimeAdded, #table2.ExtraData
FROM #table2
LEFT JOIN #table1 on #table1.id = #table2.id
WHERE #table1.id is null

6
Lubię EXCEPT SELECT
Bryan

1
Pierwszy raz użyłem Z WYJĄTKIEM. Prosty i elegancki.
jhowe

Ale Z WYJĄTKIEM może nie być wydajne w przypadku operacji masowych.
Aasish Kr. Sharma

Z WYJĄTKIEM nie jest tak wydajne.
Biswa,

1
@Biswa: Nie według tych testów. Kod jest dostępny na stronie. Uruchom go w swoim systemie, aby zobaczyć porównanie wyników.

25

Użyłbym scalenia:

create PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   with data as (select @_DE as de, @_ASSUNTO as assunto, @_DATA as data)
   merge EmailsRecebidos t
   using data s
      on s.de = t.de
     and s.assunte = t.assunto
     and s.data = t.data
    when not matched by target
    then insert (de, assunto, data) values (s.de, s.assunto, s.data);
END

Idę z tym, ponieważ jego
hodowca

Chciałbym użyć scalania ... ale to nie działa dla tabel zoptymalizowanych pod kątem pamięci.
Don Sam

20

Wypróbuj poniższy kod

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   INSERT INTO EmailsRecebidos (De, Assunto, Data)
   select @_DE, @_ASSUNTO, @_DATA
   EXCEPT
   SELECT De, Assunto, Data from EmailsRecebidos
END

11

INSERTKomenda nie posiada WHEREklauzuli - musisz napisać to tak:

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
  (@_DE nvarchar(50),
   @_ASSUNTO nvarchar(50),
   @_DATA nvarchar(30) )
AS
BEGIN
   IF NOT EXISTS (SELECT * FROM EmailsRecebidos 
                   WHERE De = @_DE
                   AND Assunto = @_ASSUNTO
                   AND Data = @_DATA)
   BEGIN
       INSERT INTO EmailsRecebidos (De, Assunto, Data)
       VALUES (@_DE, @_ASSUNTO, @_DATA)
   END
END

1
Musisz obsłużyć błędy w tej procedurze, ponieważ zdarzają się przypadki, w których wstawianie nastąpi między sprawdzeniem a wstawieniem.
Filip De Vos

@FilipDeVos: true - możliwość, być może mało prawdopodobna, ale wciąż możliwość. Słuszna uwaga.
marc_s

Co się stanie, jeśli zawrzesz oba w ramach transakcji? Czy to zablokowałoby taką możliwość? (Nie jestem ekspertem od transakcji, więc proszę wybaczyć, jeśli jest to głupie pytanie.)
David

1
Zobacz stackoverflow.com/a/3791506/1836776, aby uzyskać dobrą odpowiedź na pytanie, dlaczego transakcja tego nie rozwiązuje, @David.
Marc Durdin,

W instrukcji IF: nie ma potrzeby używania BEGIN & END, jeśli liczba wymaganych wierszy poleceń jest tylko jedna, nawet jeśli użyłeś więcej niż jednej linii, więc możesz ją pominąć tutaj.
Wessam El Mahdy

11

Zrobiłem to samo z SQL Server 2012 i zadziałało

Insert into #table1 With (ROWLOCK) (Id, studentId, name)
SELECT '18769', '2', 'Alex'
WHERE not exists (select * from #table1 where Id = '18769' and studentId = '2')

4
Oczywiście, że zadziałało, używasz tabeli tymczasowej (tzn. Nie musisz martwić się o współbieżność podczas korzystania z tabel tymczasowych).
drowa

6

W zależności od wersji (2012?) Programu SQL Server oprócz IF EXISTS można również użyć funkcji MERGE w następujący sposób:

ALTER PROCEDURE [dbo].[EmailsRecebidosInsert]
    ( @_DE nvarchar(50)
    , @_ASSUNTO nvarchar(50)
    , @_DATA nvarchar(30))
AS BEGIN
    MERGE [dbo].[EmailsRecebidos] [Target]
    USING (VALUES (@_DE, @_ASSUNTO, @_DATA)) [Source]([De], [Assunto], [Data])
         ON [Target].[De] = [Source].[De] AND [Target].[Assunto] = [Source].[Assunto] AND [Target].[Data] = [Source].[Data]
     WHEN NOT MATCHED THEN
        INSERT ([De], [Assunto], [Data])
        VALUES ([Source].[De], [Source].[Assunto], [Source].[Data]);
END

2

Inny SQL, ta sama zasada. Wstaw tylko, jeśli klauzula, w której nie istnieje, zawiedzie

INSERT INTO FX_USDJPY
            (PriceDate, 
            PriceOpen, 
            PriceLow, 
            PriceHigh, 
            PriceClose, 
            TradingVolume, 
            TimeFrame)
    SELECT '2014-12-26 22:00',
           120.369000000000,
           118.864000000000,
           120.742000000000,
           120.494000000000,
           86513,
           'W'
    WHERE NOT EXISTS
        (SELECT 1
         FROM FX_USDJPY
         WHERE PriceDate = '2014-12-26 22:00'
           AND TimeFrame = 'W')

-1

Jak wyjaśniono w poniższym kodzie: Wykonaj poniższe zapytania i zweryfikuj siebie.

CREATE TABLE `table_name` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Wstaw rekord:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_name`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
+----+--------+-----------+------+

Teraz spróbuj ponownie wstawić ten sam rekord:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Nazir', 'Kolkata', '033') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Nazir'
) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
+----+--------+-----------+------+

Wstaw inny rekord:

INSERT INTO table_name (name, address, tele)
SELECT * FROM (SELECT 'Santosh', 'Kestopur', '044') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_name WHERE name = 'Santosh'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

SELECT * FROM `table_name`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Nazir  | Kolkata   | 033  |
|  2 | Santosh| Kestopur  | 044  |
+----+--------+-----------+------+

1
Czy to nie dotyczy MySQL, a pytanie dotyczy SQL Servera?
Douglas Gaskell,

Tak, to dla MySQL.
vadiraj jahagirdar

-2

Możesz użyć GOpolecenia. Spowoduje to ponowne uruchomienie wykonywania instrukcji SQL po wystąpieniu błędu. W moim przypadku mam kilka 1000 instrukcji INSERT, w których garść tych rekordów już istnieje w bazie danych, po prostu nie wiem, które z nich. Odkryłem, że po przetworzeniu kilku 100 wykonanie kończy się z komunikatem o błędzie, że nie może, INSERTponieważ rekord już istnieje. Dość denerwujące, ale GOrozwiązanie tego. To może nie być najszybsze rozwiązanie, ale prędkość nie była moim problemem.

GO
INSERT INTO mytable (C1,C2,C3) VALUES(1,2,3)
GO
INSERT INTO mytable (C1,C2,C3) VALUES(4,5,6)
 etc ...

GOjest separatorem wsadowym? Nie pomaga w zapobieganiu powielaniu rekordów.
Dale K
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.