Wstawianie, aktualizowanie i usuwanie danych przy użyciu korespondencji SERYJNEJ
W SQL Server 2008, wstawiania, aktualizacji lub usuwania operacji można wykonać w pojedynczej instrukcja, za pomocą instrukcja korespondencji SERYJNEJ. Instrukcja korespondencji SERYJNEJ można dołączyć urządzenie źródłowe danych z tabela miejsce docelowe lub w widoku, a następnie wykonaj wielu akcji dla miejsce docelowe, na podstawie wyniki tej łączyć.Na przykład można użyć instrukcja korespondencji SERYJNEJ, aby wykonać następujące operacje:
Warunkowo wstawić lub zaktualizować wiersze w tabela miejsce docelowe.
Jeśli wiersz w tabela miejsce docelowe, aktualizacja jednego lub kilku kolumn, w przeciwnym razie Wstaw dane do nowego wiersza.
Służy do synchronizowania dwóch tabel.
Wstawianie, aktualizowanie lub usuwanie wierszy w tabela miejsce docelowe, oparte na różnice w urządzenie źródłowe danych.
Składnia korespondencji SERYJNEJ składa się z pięciu klauzule podstawowego:
Określa klauzula korespondencji SERYJNEJ, tabela lub widok, który jest miejsce docelowe z wstawiania, aktualizacji lub usuwania operacji.
Przy użyciu klauzula Określa urządzenie źródłowe danych są połączone z miejsce docelowe.
Klauzula ON określa warunki łączyć, które określają, gdzie miejsce docelowe i dopasowanie urządzenie źródłowe.
GDY klauzule (DOPASOWANE, gdy nie PASUJE BY miejsce docelowe, a nie PASUJE BY urządzenie źródłowe) określić akcje, które należy wykonać oparte na wynikach klauzuli ON i wszelkie dodatkowe kryteria wyszukiwania określonych w klauzulach podczas.
Klauzula wyjście zwraca wiersz dla każdego wiersza w miejsce docelowe, jest wstawiany, zaktualizowany lub usunięty.
Aby uzyskać pełną szczegółowe informacje o składni i reguł zobacz Korespondencji SERYJNEJ (języka Transact-SQL).
Określanie warunków wyszukiwania miejsce docelowe i urządzenie źródłowe
Ważne jest zrozumieć, jak urządzenie źródłowe i miejsce docelowe danych są scalane w jeden strumień wejściowy i jak dodatkowe kryteria wyszukiwania można używać do filtrowania poprawnie się zbędne wiersze.W przeciwnym przypadku można określić dodatkowe kryteria w taki sposób, że powoduje niepoprawne wyniki.
Wiersze w urządzenie źródłowe są dopasowywane do wierszy w obiekcie docelowym, oparte na predykat łączyć, określonych w klauzula ON.Wynik jest połączone strumień wejściowy.Jeden wstawiania, aktualizacji lub operacji usuwania jest wykonywane na wiersz danych wejściowych.W zależności od klauzule gdy określona w instrukcja wiersz wejściowy może być jeden z następujących czynności:
Pasujące pary składające się z jednego wiersza z miejsce docelowe i jedną ze urządzenie źródłowe.Jest to wynikiem, gdy klauzula MATCHED.
Wiersz z urządzenie źródłowe, które ma bez odpowiedniego wiersza w miejsce docelowe.Jest to wynikiem, gdy nie PASUJE BY miejsce docelowe klauzula.
Wiersz z obiektu docelowego, dla którego jest bez odpowiedniego wiersza urządzenie źródłowe.Jest to wynikiem, gdy nie PASUJE BY urządzenie źródłowe klauzula.
Połączenie KIEDY klauzule określona w instrukcja korespondencji SERYJNEJ Określa typ łączyć, który jest implementowany przez procesor kwerend i wpływa na wynikowej strumień wejściowy.Aby zilustrować, należy rozważyć następujący przykład źródłowe i docelowe tabele i dane.
Następująca tabela zawiera listę typów sprzężeń można i wskazuje, kiedy każdego typu jest implementowana przez optymalizator kwerendy.W tabela przedstawiono również wynikowy strumień wejściowy dla przykładu urządzenie źródłowe i tabel miejsce docelowe, kiedy pasujące do kryteriów wyszukiwania urządzenie źródłowe i docelowego danych Source.EmployeeID = Target.EmployeeID.
Typ łączyć |
Implementacja |
Przykład wyniki strumień wejściowy |
|---|---|---|
łączyć WEWNĘTRZNE |
GDY klauzula MATCHED jest tylko określona klauzula po. |
SrcEmpID Nazwa_źródła TrgEmpID TrgName -------- ------- -------- ------- ZEROWA WARTOŚĆ ZEROWA WARTOŚĆ ZEROWA WARTOŚĆ NULL |
łączyć ZEWNĘTRZNE Z LEWEJ STRONY |
Podczas nie PASUJE BY miejsce docelowe klauzula został określony, ale gdy nie DOPASOWANE przez urządzenie źródłowe klauzula nie został określony.GDY MATCHED mogą lub nie może być określony. |
SrcEmpID Nazwa_źródła TrgEmpID TrgName ------------------------------100 Mary NULL NULL 101 Sara NULL NULL Stefano 102 NULL NULL |
prawe łączyć zewnętrzne |
Po klauzula MATCHED i gdy nie DOPASOWYWANE przez urządzenie źródłowe klauzula są określone, ale gdy nie DOPASOWANE przez klauzula miejsce docelowe nie został określony. |
SrcEmpID Nazwa_źródła TrgEmpID TrgName ------------------------------NULL NULL Robert 103 Wartość NULL NULL 104 Steve |
PEŁNE łączyć ZEWNĘTRZNE |
GDY nie PASUJE BY miejsce docelowe klauzula i gdy nie DOPASOWYWANE przez urządzenie źródłowe klauzula są określone.GDY MATCHED mogą lub nie może być określony. |
SrcEmpID Nazwa_źródła TrgEmpID TrgName ------------------------------100 Mary NULL NULL 101 Sara NULL NULL Stefano 102 NULL NULL Wartość NULL NULL Robert 103 Wartość NULL NULL 104 Steve |
ANTY łączyć NACZEPA |
GDY nie DOPASOWANE przez urządzenie źródłowe klauzula jest tylko określona klauzula po. |
TrgEmpID TrgName -------- ------- 100 Maria 101 Sara 102 Stefano |
Wyniki strumień wejściowy przykład wskazują, że strumień wejściowy wyniki zależą od kombinację klauzul podczas.Załóżmy teraz chcesz wykonywać następujące akcje w tabela miejsce docelowe, w oparciu o tym strumień wejściowy:
Wstawianie wierszy z urządzenie źródłowe tabela, identyfikator pracownika nie tabela miejsce docelowe i urządzenie źródłowe nazwisko pracownika, który rozpoczyna się od firmy ".
Usuwanie wierszy w tabela miejsce docelowe, gdy rozpoczyna się nazwa pracownika docelowego 's ' i pracownika, identyfikator nie istnieje w urządzenie źródłowe tabela.
Aby wykonać te akcje, wymagane są następujące klauzule podczas:
GDY NIE ZOSTAŁY DOPASOWANE PRZEZ WSTAWIANIE miejsce docelowe NASTĘPNIE
JEŚLI NIE ZOSTAŁY DOPASOWANE PRZEZ urządzenie źródłowe USUŃ
Zgodnie z opisem w poprzedniej tabela, gdy oba, gdy nie PASUJE klauzule są określone, wynikowy strumień wejściowy jest pełne łączyć zewnętrzne z urządzenie źródłowe i tabel.Teraz, gdy strumień wejściowy wyniki są znane, należy rozważyć sposób wstawiania, aktualizacji i akcje usuwania zostaną zastosowane w strumieniu wejściowym.
Jak wspomniano wcześniej, klauzule podczas określania działaniach, które należy wykonać na podstawie wyniki klauzula ON i wszelkie dodatkowe kryteria wyszukiwania określonych w klauzulach gdy.W wielu przypadkach warunki wyszukiwania określonych w klauzula ON daje wymagane strumień wejściowy.W scenariuszu przykład operacje wstawiania i usuwania wymagają jednak dodatkowe filtrowanie, aby ograniczyć wiersze usterce do tych, których nazwiska pracownika, którego nazwa rozpoczyna się od firmy ".W poniższym przykładzie warunki filtrowania są stosowane do podczas nie PASUJE BY miejsce docelowe i gdy nie DOPASOWYWANE przez urządzenie źródłowe.Dane wyjściowe z instrukcja wskazuje, że oczekiwano wiersze z strumień wejściowy są poprawione, wstawiony lub usunięty.
Poniżej przedstawiono wyniki klauzula OUTPUT.
Akcja $ IDPracownika EmployeeName IDPracownika EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(dotyczy wiersze 3)
Zmniejszanie liczby wierszy w strumieniu wejściowym na początku procesu przez określenie dodatkowych warunek wyszukiwania klauzula ON (na przykład przez określenie ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%') może wydawać się, aby poprawić wydajność kwerendy. Jednak robić tak może spowodować nieprawidłowe i nieoczekiwane wyniki.Ponieważ dodatkowe kryteria wyszukiwania warunki określone w klauzula ON nie są używane do dopasowania urządzenie źródłowe i miejsce docelowe danych, można je misapplied.
W poniższym przykładzie pokazano, jak nieprawidłowe wyniki mogą występować.Warunek wyszukiwania do dopasowania urządzenie źródłowe i obie tabele docelowe i stan dodatkowe kryteria wyszukiwania do filtrowania wierszy są określone w klauzula ON.Ponieważ warunek dodatkowe kryteria wyszukiwania nie jest wymagane, aby określić urządzenie źródłowe i docelowych dopasowania, wstawianie i usuwanie akcji są stosowane do wprowadzania wszystkich wierszy.W efekcie warunek filtrowania EmployeeName LIKE 'S%' jest ignorowana. Gdy uruchomione w instrukcja, dane wyjściowe inserted i deleted tabele pokazuje, że dwa wiersze zostaną nieprawidłowo zmienione: Mary jest nieprawidłowo usuwany z tabela miejsce docelowe i Robert niepoprawnie zostanie wstawiony.
Poniżej przedstawiono wyniki klauzula OUTPUT.
Akcja $ IDPracownika EmployeeName IDPracownika EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Robert NULL NULL
INSERT 104 Steve NULL NULL
(dotyczy wiersze 5)
Wskazówki dotyczące warunek wyszukiwania
Warunki wyszukiwania używane w celu dopasowania wierszy źródłowych i miejsce docelowe i warunki dodatkowe kryteria wyszukiwania używane do filtrowania wierszy z jednej urządzenie źródłowe lub miejsce docelowe musi być określona poprawnie zapewniające, że poprawne wyniki są otrzymywane.Zaleca się zgodnie z poniższymi wskazówkami:
Określ tylko warunki wyszukiwania w ON <merge_search_condition> Klauzula, które określają kryteria dopasowywania danych urządzenie źródłowe i tabel. Oznacza to, określ tylko te kolumny z tabela miejsce docelowe, które są porównywane z odpowiednimi kolumnami urządzenie źródłowe tabela.
Nie należy umieszczać porównań inne wartości, takie jak stała.
Aby odfiltrować wiersze w tabelach źródłowych lub miejsce docelowe, użyj jednej z następujących metod:
Określ warunek wyszukiwania do filtrowania w klauzula gdy odpowiedni wiersz.Na przykład WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Definiowanie widoku na urządzenie źródłowe lub cel, zwraca filtrowane wiersze i odwołać widoku jako urządzenie źródłowe lub tabela miejsce docelowe.Jeśli widok jest zdefiniowany w tabela miejsce docelowe, wszystkie akcje przed nim muszą spełniać warunki w celu zaktualizowania widoków.Aby uzyskać więcej informacji na temat aktualizacji danych przy użyciu widoku zobacz Modifying Data Through a View.
Za pomocą WITH <Typowe wyrażenie tabela> Klauzula, aby odfiltrować wiersze w tabelach źródłowych lub miejsce docelowe. Ta metoda jest podobna do określania dodatkowe kryteria wyszukiwania w klauzula ON i może powodować nieprawidłowe wyniki.Zaleca się, że należy unikać stosowania tej metoda lub dokładnie przetestować przed wdrożeniem go.
Przykłady
A.Za pomocą prostej instrukcja korespondencji SERYJNEJ do wykonywania operacji INSERT i UPDATE
Załóżmy, że masz FactBuyingHabits tabela w bazie danych magazyn danych, która śledzi ostatnią data każdego klienta zakupione określonego produktu.W przypadku drugiej tabela Zakupy, w OLTP bazy danych rekordy zakupów w danym tygodniu.Każdego tygodnia, które chcesz dodać wiersze produktów, określonych nabywców nigdy nie zakupiono przed z Zakupy tabelaFactBuyingHabits tabela.Dla wierszy zakupu produktów klientom one już kupili przed, po prostu chcesz zaktualizować data zakupu w FactBuyingHabits tabela.Te Wstawianie i aktualizacja operacje mogą być wykonywane w pojedynczej instrukcja przy użyciu korespondencji SERYJNEJ.
W poniższym przykładzie najpierw tworzy tabele Purchases i FactBuyingHabits i ładowania ich z niektórych przykładowych danych. Zwiększa wydajność w deklaracjach korespondencji SERYJNEJ, gdy UNIQUE indeksy są tworzone w kluczu łączyć, więc stosowanie indeksów klastrowanych są tworzone poprzez utworzenie ograniczenia klucz podstawowy na ProductID kolumna w obu tabelach.
In this example, Purchases contains purchases for the week of August 21, 2006.FactBuyingHabits contains purchases for the prior week; ordinarily this table would be populated with rows dating back much earlier.
Tabele są teraz wypełniane następujące dane:
dbo.Purchases
Identyfikator klienta IDProduktu PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
Identyfikator klienta IDProduktu LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Należy zauważyć, że są wspólne dla obu tabel dwa wiersze klienta produktu: Klient 11794 zakupił 707 produktu w ciągu bieżącego tygodnia, a także poprzedniego tygodnia; to samo dotyczy zakupu klienta 15160 870 produktu. Dla tych wierszy firma Microsoft aktualizować FactBuyingHabits data zarejestrowanych dla tych zakupów w Zakupy przy użyciu gdy DOPASOWYWANE THEN klauzula.Firma Microsoft wstawione wszystkie inne wiersze FactBuyingHabits przy użyciu gdy DOPASOWYWANE THEN nie klauzula.
B.Wykonanie operacji aktualizacji i usuwania
W poniższym przykładzie użyto korespondencji SERYJNEJ, aby zaktualizować ProductInventory Tabela w AdventureWorks codziennie przykładowej bazy danych, na podstawie zamówień, które są przetwarzane w SalesOrderDetail Tabela. Za pomocą następujących instrukcja korespondencji SERYJNEJ, Quantity Kolumna ProductInventory Tabela jest aktualizowana przez odjęcie liczby zamówień złożonych codziennie dla każdego produktu. Jeżeli liczba zamówień dla produktu powoduje, że zapasy produktu mają być umieszczane na 0 lub poniżej, w wierszu dla tego produktu jest usuwany z ProductInventory Tabela. Należy zauważyć, że urządzenie źródłowe tabela są łączone w ProductID Kolumna. Jeśli zostały nie to, więcej niż jeden ProductID w urządzenie źródłowe tabela może pasować do tabela miejsce docelowe i spowodować, że instrukcja korespondencji SERYJNEJ zwraca błąd.
C.Wykonywanie operacji INSERT, UPDATE i DELETE
W poniższym przykładzie użyto korespondencji SERYJNEJ do wstawiania, aktualizacji lub usuwania wierszy w tabela miejsce docelowe, w oparciu o różnicami urządzenie źródłowe danych.Należy rozważyć małej firmie z pięciu działów, każdy z działu menedżera.Firma decyduje się na re-organize jego działów.Aby zaimplementować wyniki reorganizacji w dbo.Departments tabela miejsce docelowe, instrukcja korespondencji SERYJNEJ musi implementować następujące zmiany:
Niektóre działy istniejących nie ulegnie zmianie.
Niektóre istniejące działy będą miały nowe menedżerów.
Nowo tworzonych jest kilka działów.
Niektóre działy nie będą istniały po reorganizacji.
Następujący kod tworzy w tabela miejsce docelowe dbo.Departments i wypełnia ją z menedżerów.
Zmiany organizacyjne do służby są przechowywane w urządzenie źródłowe tabela dbo.Departments_delta. Następujący kod tworzy i wypełnia tę tabela:
Na koniec odzwierciedlając reorganizacji firmy w tabela miejsce docelowe, następujący kod używa instrukcja korespondencji SERYJNEJ do porównania z tabela źródłowej dbo.Departments_delta, z tabela miejsce docelowe dbo.Departments. W klauzula ON instrukcja zdefiniowano warunek wyszukiwania dla porównania.Na podstawie wyniki porównania, pobierane są następujące akcje.
Działy, które istnieją w obu tabelach są aktualizowane w tabela miejsce docelowe o nowe nazwy i/lub menedżerowie nowej tabela Departments. Jeśli nie ma zmian, nic się nie jest aktualizowany.Jest to możliwe w gdy DOPASOWYWANE THEN klauzula.
Wszelkie działów w Departments_delta które nie istnieją w pliku Departments są wstawiane do Departments. Jest to możliwe w podczas nie PASUJE THEN klauzula.
Wszelkie działów w Departments które nie istnieją w tabela źródłowej Departments_delta zostaną usunięte z Departments. Jest to możliwe w po nie, BY MATCHED THEN urządzenie źródłowe klauzula.