SQL Server wstaw, jeśli nie istnieje najlepsze rozwiązanie

Mam Competitionstabelę wyników, która zawiera nazwiska członków zespołu i ich ranking na jednej ręce.

Z drugiej strony muszę prowadzić tabelę unikalnych nazw konkurentów :

CREATE TABLE Competitors (cName nvarchar(64) primary key)

Teraz mam około 200 000 wyników w pierwszej tabeli i gdy tabela konkurentów jest pusta , mogę wykonać to:

INSERT INTO Competitors SELECT DISTINCT Name FROM CompResults

A zapytanie zajmuje tylko około 5 sekund, aby wstawić około 11 000 nazw.

Jak dotąd nie jest to krytyczna aplikacja, więc mogę rozważyć obcinanie tabeli konkurencji raz w miesiącu, kiedy otrzymam nowe wyniki zawodów z około 10 000 wierszami.

Ale jaka jest najlepsza praktyka, gdy dodaje się nowe wyniki, z nowymi ORAZ istniejącymi konkurentami? Nie chcę skracać istniejącej tabeli konkurentów

Muszę wykonać instrukcję INSERT tylko dla nowych konkurentów i nic nie robić, jeśli istnieją.

Semantycznie pytasz „wstaw konkurentów tam, gdzie jeszcze nie istnieje”:

INSERT Competitors (cName)
SELECT DISTINCT Name
FROM CompResults cr
WHERE
   NOT EXISTS (SELECT * FROM Competitors c
              WHERE cr.Name = c.cName)

Inną opcją jest pozostawienie dołączenia do tabeli wyników z istniejącą tabelą konkurentów i znalezienie nowych konkurentów, filtrując odrębne rekordy, które nie pasują do połączenia:

INSERT Competitors (cName)
SELECT  DISTINCT cr.Name
FROM    CompResults cr left join
        Competitors c on cr.Name = c.cName
where   c.cName is null

Nowa składnia MERGE oferuje również kompaktowy, elegancki i wydajny sposób na zrobienie tego:

MERGE INTO Competitors AS Target
USING (SELECT DISTINCT Name FROM CompResults) AS Source ON Target.Name = Source.Name
WHEN NOT MATCHED THEN
    INSERT (Name) VALUES (Source.Name);

Nie wiem, dlaczego nikt jeszcze tego nie powiedział;

NORMALIZOWAĆ.

Masz stół, który modeluje zawody? Zawody składają się z zawodników? Potrzebujesz odrębnej listy Zawodników w co najmniej jednym Konkursie ......

Powinieneś mieć następujące tabele .....

CREATE TABLE Competitor (
    [CompetitorID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitorName] NVARCHAR(255)
    )

CREATE TABLE Competition (
    [CompetitionID] INT IDENTITY(1,1) PRIMARY KEY
    , [CompetitionName] NVARCHAR(255)
    )

CREATE TABLE CompetitionCompetitors (
    [CompetitionID] INT
    , [CompetitorID] INT
    , [Score] INT

    , PRIMARY KEY (
        [CompetitionID]
        , [CompetitorID]
        )
    )

Z ograniczeniami dotyczącymi konkurencji.Competitors.CompetitionID i CompetitorID wskazującymi na inne tabele.

Z tego rodzaju strukturą tabeli - wszystkie klucze są proste INTS - nie wydaje się, aby był dobry NATURALNY KLUCZ, który pasowałby do modelu, więc myślę, że klucz SURROGATE jest tutaj dobrze dopasowany.

Więc jeśli masz to, aby uzyskać odrębną listę konkurentów w konkretnej konkurencji, możesz wysłać zapytanie w ten sposób:

DECLARE @CompetitionName VARCHAR(50) SET @CompetitionName = 'London Marathon'

    SELECT
        p.[CompetitorName] AS [CompetitorName]
    FROM
        Competitor AS p
    WHERE
        EXISTS (
            SELECT 1
            FROM
                CompetitionCompetitor AS cc
                JOIN Competition AS c ON c.[ID] = cc.[CompetitionID]
            WHERE
                cc.[CompetitorID] = p.[CompetitorID]
                AND cc.[CompetitionName] = @CompetitionNAme
        )

A jeśli chcesz uzyskać wynik dla każdego konkursu, w którym jest zawodnik:

SELECT
    p.[CompetitorName]
    , c.[CompetitionName]
    , cc.[Score]
FROM
    Competitor AS p
    JOIN CompetitionCompetitor AS cc ON cc.[CompetitorID] = p.[CompetitorID]
    JOIN Competition AS c ON c.[ID] = cc.[CompetitionID]

A kiedy masz nowe zawody z nowymi konkurentami, po prostu sprawdź, którzy z nich już istnieją w tabeli Konkurenci. Jeśli już istnieją, nie wstawiaj ich do pozycji Konkurenta dla tych konkurentów i nie wstawiaj nowych.

Następnie wstawiasz nowy Konkurs do Konkursu i na koniec po prostu tworzysz wszystkie linki w Konkurenci.

Będziesz musiał dołączyć do stołów razem i uzyskać listę unikalnych konkurentów, których jeszcze nie ma w Competitors.

Spowoduje to wstawienie unikalnych rekordów.

INSERT Competitors (cName) 
SELECT DISTINCT Name
FROM CompResults cr LEFT JOIN Competitors c ON cr.Name = c.cName
WHERE c.Name IS NULL

Może się zdarzyć, że wstawka będzie musiała zostać wykonana szybko, bez możliwości oczekiwania na wybór unikalnych nazw. W takim przypadku możesz wstawić unikalne nazwy do tabeli tymczasowej, a następnie użyć tej tabeli tymczasowej do wstawienia do tabeli rzeczywistej. Działa to dobrze, ponieważ całe przetwarzanie odbywa się w czasie wstawiania do tabeli tymczasowej, więc nie ma to wpływu na prawdziwą tabelę. Następnie, po zakończeniu całego przetwarzania, dokonujesz szybkiego wstawienia do prawdziwego stołu. Mogę nawet zawinąć ostatnią część, w której wstawiasz do prawdziwej tabeli, wewnątrz transakcji.

Odpowiedzi powyżej, które mówią o normalizacji, są świetne! Ale co, jeśli znajdziesz się w sytuacji takiej jak ja, w której nie możesz dotykać schematu lub struktury bazy danych w obecnej postaci? Np. DBA są „bogami” i wszystkie sugerowane wersje idą do / dev / null?

W związku z tym wydaje mi się, że odpowiedź na to pytanie została również udzielona przez ten post dotyczący przepełnienia stosu w odniesieniu do wszystkich powyższych użytkowników podających próbki kodu.

Ponownie publikuję kod z WSTAW WARTOŚCI, GDZIE NIE ISTNIEJE, co pomogło mi najbardziej, ponieważ nie mogę zmienić żadnych bazowych tabel bazy danych:

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

Powyższy kod używa innych pól niż te, które masz, ale masz ogólne pojęcie o różnych technikach.

Zauważ, że zgodnie z oryginalną odpowiedzią na Stack Overflow, ten kod został skopiowany stąd .

W każdym razie chodzi mi o to, że „najlepsza praktyka” często sprowadza się do tego, co możesz, a czego nie możesz zrobić tak dobrze, jak teoria.

• Jeśli jesteś w stanie normalizować i generować indeksy / klucze - świetnie!
• Jeśli nie i masz uciekać się do kodowania hacków, takich jak ja, mam nadzieję, że powyższe pomoże.

Powodzenia!

Normalizacja tabel operacyjnych, zgodnie z sugestią Transact Charlie, jest dobrym pomysłem i pozwoli z czasem zaoszczędzić wielu bólów głowy i problemów - ale są takie rzeczy, jak tabele interfejsów , które obsługują integrację z systemami zewnętrznymi, oraz tabele raportowania , które obsługują takie rzeczy, jak analityczne przetwarzanie; a tego typu tabele niekoniecznie powinny być znormalizowane - w rzeczywistości bardzo często jest to o wiele wygodniejsze i bardziej wydajne, aby nie były .

W tym przypadku myślę, że propozycja Transact Charlie dotycząca twoich stołów operacyjnych jest dobra.

Ale dodałbym indeks (niekoniecznie unikalny) do CompetitorName w tabeli Competitors, aby wspierać wydajne łączenia na CompetitorName dla celów integracji (ładowanie danych z zewnętrznych źródeł), a następnie wstawiłbym do miksu tabelę interfejsów: CompetitionResults.

Wyniki konkurencji powinny zawierać wszelkie dane, które zawierają wyniki konkurencji. Celem tabeli interfejsu, takiej jak ta, jest jak najszybsze i najłatwiejsze obcinanie i ponowne ładowanie jej z arkusza Excela lub pliku CSV lub jakiejkolwiek innej formy, w której znajdują się te dane.

Tej tabeli interfejsu nie należy uważać za część znormalizowanego zestawu tabel operacyjnych. Następnie możesz dołączyć do CompetitionResults zgodnie z sugestią Richarda, aby wstawić rekordy do konkurentów, którzy jeszcze nie istnieją, i zaktualizować te, które istnieją (na przykład, jeśli faktycznie masz więcej informacji o konkurentach, takich jak ich numer telefonu lub adres e-mail).

Chciałbym zwrócić uwagę na jedną rzecz - w rzeczywistości wydaje mi się, że nazwa konkurenta jest unikalna w twoich danych . Na 200 000 konkurentów możesz mieć na przykład 2 lub więcej Davida Smithów. Dlatego radziłbym zebrać więcej informacji od konkurentów, takich jak ich numer telefonu lub adres e-mail, lub coś, co z większym prawdopodobieństwem będzie unikalne.

Twoja tabela operacyjna, Konkurenci, powinna mieć tylko jedną kolumnę dla każdego elementu danych, który składa się na złożony klucz naturalny; na przykład powinien mieć jedną kolumnę na podstawowy adres e-mail. Ale tablica interfejsu powinna mieć miejsce na stare i nowe wartości dla podstawowego adresu e-mail, tak aby stara wartość mogła być użyta do wyszukania rekordu w Konkurenci i zaktualizowania tej części do nowej wartości.

Więc CompetitionResults powinno mieć kilka "starych" i "nowych" pól - oldEmail, newEmail, oldPhone, newPhone, itp. W ten sposób możesz utworzyć klucz złożony, w konkurencji, z CompetitorName, Email i Phone.

Następnie, gdy uzyskasz wyniki zawodów, możesz skrócić i ponownie załadować tabelę CompetitionResults z arkusza Excela lub czegokolwiek innego i uruchomić jedną wydajną wstawkę, aby wstawić wszystkich nowych konkurentów do tabeli konkurentów, oraz pojedynczą, wydajną aktualizację, aby zaktualizować wszystkie informacje o istniejących zawodnikach z Wyniki Konkursu. I możesz zrobić jedno wstawienie, aby wstawić nowe wiersze do tabeli CompetitionCompetitors. Te rzeczy można wykonać w procedurze składowanej ProcessCompetitionResults, którą można wykonać po załadowaniu tabeli CompetitionResults.

To rodzaj podstawowego opisu tego, co wielokrotnie widziałem w prawdziwym świecie z Oracle Applications, SAP, PeopleSoft i listą innych pakietów oprogramowania dla przedsiębiorstw.

Ostatni komentarz, jaki mam, to ten, który napisałem wcześniej na temat SO: Jeśli utworzysz klucz obcy, który zapewni, że Konkurent istnieje w tabeli Konkurentów, zanim będziesz mógł dodać wiersz z tym Konkurentem do Konkurencji, upewnij się, że klucz obcy jest ustawiony na kaskadowe aktualizacje i usuwanie . W ten sposób, jeśli chcesz usunąć konkurenta, możesz to zrobić, a wszystkie wiersze powiązane z tym konkurentem zostaną automatycznie usunięte. W przeciwnym razie, domyślnie klucz obcy będzie wymagał usunięcia wszystkich powiązanych wierszy z listy Konkurenci, zanim pozwoli Ci usunąć konkurenta.

(Niektórzy uważają, że niekaskadowe klucze obce są dobrym środkiem ostrożności, ale z mojego doświadczenia wynika, że ​​są one po prostu przerażającym bólem w tyłku, które często są po prostu wynikiem przeoczenia i tworzą mnóstwo pracy DBA. Radzenie sobie z ludźmi, którzy przypadkowo usuwają rzeczy, jest powodem, dla którego pojawiają się okna dialogowe „czy na pewno” oraz różne typy regularnych kopii zapasowych i nadmiarowych źródeł danych. O wiele, dużo częściej zdarza się, że faktycznie chce się usunąć konkurenta, którego dane to wszystkie na przykład pomieszane, niż przypadkowe usunięcie jednego, a następnie „O nie! Nie chciałem tego zrobić! A teraz nie mam wyników ich zawodów! Aaaach!” To drugie jest z pewnością dość powszechne, więc trzeba być na to przygotowanym, ale to pierwsze jest znacznie bardziej powszechne,więc najłatwiejszym i najlepszym sposobem przygotowania się do pierwszego, imo, jest wykonanie kaskadowych aktualizacji i usuwania kluczy obcych).

Ok, zadawano to 7 lat temu, ale myślę, że najlepszym rozwiązaniem jest całkowite zrezygnowanie z nowego stołu i zrobienie tego jako niestandardowego widoku. W ten sposób nie duplikujesz danych, nie martwisz się o unikalne dane i nie naruszają one faktycznej struktury bazy danych. Coś takiego:

CREATE VIEW vw_competitions
  AS
  SELECT
   Id int
   CompetitionName nvarchar(75)
   CompetitionType nvarchar(50)
   OtherField1 int
   OtherField2 nvarchar(64)  --add the fields you want viewed from the Competition table
  FROM Competitions
GO

Można tutaj dodać inne elementy, takie jak łączenia w innych tabelach, klauzule WHERE itp. Jest to najprawdopodobniej najbardziej eleganckie rozwiązanie tego problemu, ponieważ teraz możesz po prostu zapytać o widok:

SELECT *
FROM vw_competitions

... i dodaj dowolne klauzule WHERE, IN lub EXISTS do zapytania widoku.