Ekleme, güncelleştirme ve BIRLEŞTIRME kullanarak veri silme
InSQL Server 2008ekleme, güncelleştirme veya silme işlemleri tek bir deyim kullanarak gerçekleştirebilir, birleştirme deyimini.Veri birleştirmek birleştirme ifade veren kaynak hedef tablo veya görünümü ve bu birleştirmek sonuçlarına dayalı hedef karşı birden fazla eylemi gerçekleştirin.Örneğin, birleştirme İŞLEMİNİ kullanabilirsiniz deyim aşağıdaki işlemleri gerçekleştirmek için:
Koşullu olarak ekleme veya güncelleştirme hedef satırların tablo.
Hedef satır varsa, tablo, bir veya birkaç sütun güncelleştirme; aksi durumda, verileri yeni bir satır ekleyin.
İki tablo eşitleyin.
Eklemek, güncelleştirmek veya hedef satırları silmek tablo farklılıkları kaynak verilerle bağlı.
BİRLEŞTİRME sözdizimi beş birincil yan oluşur:
Birleştirme yan tümce belirler tablo veya hedefi olan ekleme, görünümü güncelleştirme veya silme işlemleri.
Kullanmayı yan belirtir verileri kaynak hedef. ile birleştirilmiş
ON yan tümce yeri belirleyen birleştirmek koşulları belirtir hedef ve kaynak eşleşme.
Zaman yan (eşleşen, NOT eşleşen BY HEDEFİ ve NOT eşleşen BY kaynak) göre ON yan tümce ve bir ek arama ölçütleri belirttiğiniz zaman yan tümceleri. sonuçlarını almak için eylemleri belirleyin
Çıktı yan tümce eklendiğinde, güncelleştirildiğinde veya silindiğinde, hedef her satır için bir satır döndürür.
Kurallar ve sözdizimi hakkında tam bilgi için bkz:BIRLEŞTIRME (Transact-SQL).
Kaynak ve hedef arama koşullarını belirleme
Kaynak ve hedef veri tek bir giriş akımına nasıl birleştirilir ve gerekli olmayan satırları ek arama ölçütleri için doğru kullanılabilir nasıl filtre önemlidir.Aksi durumda, hatalı sonuçlar üreten bir şekilde ek arama ölçütleri belirtebilir.
Kaynak satır satır açık belirtilen birleştirmek koşulunu temel hedef ile eşleştirilir yan tümce.Birleşik Giriş akışı sonucudur.Giriş satırı bir ekleme, güncelleştirme veya silme işlemi gerçekleştirilir.Belirtilen zaman yan bağlı deyim, giriş satırı aşağıdakilerden biri olabilir:
Ve bir hedef için bir satır içeren bir eşleşen çifti kaynak.Bu sonucu OLDUĞUNDA, MATCHED yan tümce.
Bir satıra karşılık gelen satır hedef olan kaynak.Bu sonucu OLDUĞUNDA, NOT eşleşen hedef BY yan tümce.
Bir satıra karşılık gelen satır veri kaynağına sahip hedef.Bu sonucu OLDUĞUNDA, NOT eşleşen kaynak BY yan tümce.
Ne zaman sorgu işlemcisi tarafından uygulanır ve sonuç Giriş akışı etkiler birleştirmek türünü belirler yan tümceleri birleştirmek deyiminde belirtilen bileşimi.Göstermek için aşağıdaki örnek kaynak ve hedef tabloları ve verileri göz önünde bulundurun.
Aşağıdaki tabloda, olası birleştirmek türlerini listeler ve sorgu iyileştiricisi tarafından her zaman uygulanan gösterir.Tablo kaynak ve hedef tablo, kaynak ve hedef verileri eşleştirmek için arama ölçütü de bu örnek için elde edilen giriş akışı gösterirSource.EmployeeID = Target.EmployeeID.
birleştirmek türü |
Uygulama |
Örneğin Giriş akışı sonuçlar |
|---|---|---|
İÇ birleştirmek |
OLDUĞUNDA ise yalnızca belirtilen zaman yan MATCHED yan tümce. |
SrcEmpID KaynakAdı TrgEmpID TrgName -------- ------- -------- ------- null null null null |
SOL dış birleştirmek |
ZAMAN NOT eşleşen hedef BY yan tümcesinde belirtilen zaman NOT eşleşen kaynak BY yan tümcesinde belirtilir.ZAMAN MATCHED olabilir veya belirtilmemiş. |
SrcEmpID KaynakAdı TrgEmpID TrgName ------------------------------100 Mary null null 101 Sara null null 102 Şirketinden Stefano null null |
SAĞ DIŞ BİRLEŞİM |
WHEN the MATCHED yan tümce ve WHEN the NOT eşleşen kaynak BY yan tümcesinde belirtilir, ancak WHEN the BY eşleşen değil hedef yan belirtilmemiş. |
SrcEmpID KaynakAdı TrgEmpID TrgName ------------------------------NULL NULL 103 CAN null null 104 Emre |
TAM DIŞ birleştirmek |
ZAMAN NOT eşleşen hedef BY yan tümce ve WHEN the NOT eşleşen kaynak BY yan tümcesinde belirtilir.ZAMAN MATCHED olabilir veya belirtilmemiş. |
SrcEmpID KaynakAdı TrgEmpID TrgName ------------------------------100 Mary null null 101 Sara null null 102 Şirketinden Stefano null null null null 103 CAN null null 104 Emre |
YARI BİRLEŞTİRME KORUMA |
OLDUĞUNDA ise yalnızca belirtilen zaman yan NOT eşleşen kaynak BY yan tümce. |
TrgEmpID TrgName -------- ------- 100 CAN 101 Sara 102 Şirketinden Stefano |
Örneğin Giriş akışı sonuçlar bağlı OLDUĞUNDA yan birleşimi Giriş akışı sonuçlar gösterir.Şimdi hedef aşağıdaki eylemleri gerçekleştirmek istediğinizi varsayalım tablo giriş Bu akışta göre:
Hedef Tablo ve kaynak çalışanın adı, kimliği yok çalışan ile başlatıldığında kaynak tablosundan bir satır eklemek getirdiği '.
Hedef satır silmek tablo başladığında hedef çalışan adı ile getirdiği ' ve kimliği veri kaynağında yok çalışan tablo.
Bu eylemleri gerçekleştirmek için aşağıdaki zaman yan tümcesi gereklidir:
HEDEF İLE EŞLEŞEN, DAHA SONRA EKLE
KAYNAK TARAFINDAN EŞLEŞEN, SİLİN
Önceki'de açıklandığı gibi tablo, OLDUĞUNDA her iki yan eşleşen NOT belirtilirse, sonuç Giriş akışı olan bir tam dış birleştirmek kaynak ve hedef tablo s.Giriş akışı sonuçlar bilinen artık, ekleme, güncelleştirme ve silme işlemleri için Giriş akışı nasıl uygulanacağını göz önünde bulundurun.
Daha önce belirtildiği gibi zaman yan gerçekleştirilecek eylemleri belirtmek temel sonuçlar ON yan tümce ve zaman yan tümcelerini belirtilen herhangi bir ek arama ölçütleri.Çoğu durumda, arama koşulu ON belirtilen yan tümce gerekli Giriş akışı oluşturur.Ancak, etkilenen satır ile başlayan bir çalışan adı olan kayıtlarla sınırlandırmak için ek süzme örnek senaryoda, ekleme ve silme eylemleri gerektirir getirdiği '.Aşağıdaki örnekte, filtre koşulları için zaman uygulanan değil eşleşen BY hedef ve WHEN the NOT eşleşen BY kaynak.Çıktısı deyim Giriş akışı'den beklenen satırlar düzeltildi, eklenen, silinen veya gösterir.
Burada, sonuçlar, OUTPUT yan tümce.
$ Eylem ÇalışanNo EmployeeName ÇalışanNo EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE boş 102 şirketinden Stefano NULL
104 Emre INSERT null null
(3 satırları etkilenen)
ON yan tümce için ek bir arama koşulu belirterek işlemin ilk aşamalarında Giriş akışı satır sayısını azaltmayı (örneğin, belirterek ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%'). sorgu performansını artırmak için görünebilirAncak bunun büyük bir şekilde beklenmeyen ve yanlış sonuçlara neden olabilir.Ek arama koşulları ON belirtildiği için yan tümce kullanılmayan kaynak ve hedef verileri eşleştirmek için misapplied.
Aşağıdaki örnek, nasıl yanlış sonuçlar gösterir ortaya çıkabilir.Kaynak ve hedef tabloları ve satırlara filtre uygulama için ek bir arama koşulu ile eşleştirmek için arama koşulu her ikisi de belirtilirse açık yan tümce.Ek arama koşulu kaynak ve hedef eşleşen belirlemek için gerekli olduğundan, ekleme ve silme eylemleri tüm giriş satırlara uygulanır.Filtreleme de etkili koşulEmployeeName LIKE 'S%'göz ardı edilir.İfade çalıştırıldığında, çıktı,insertedvedeletedtablolarını gösterir iki yanlış değişiklik: Mary hedef yanlış silinmiş tablo, ve CAN yanlış eklenir.
Burada, sonuçlar, OUTPUT yan tümce.
$ Eylem ÇalışanNo EmployeeName ÇalışanNo EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 CAN
DELETE NULL NULL 101 Sara
DELETE boş 102 şirketinden Stefano NULL
103 CAN INSERT null null
104 Emre INSERT null null
(5 satırları etkilenen)
Arama koşulu yönergeleri
Arama koşullarını kaynak ve hedef satırları eşleştirmek için kullanılan ek arama koşulları ya da kaynak satırları süzmek için kullanılan ve doğru sonuçlar elde edilen emin olmak için hedef doğru belirtilmelidir.Aşağıdaki yönergeleri öneririz:
Yalnızca arama koşulları belirlemeON <merge_search_condition>yan tümce kaynak ve hedef tablolar. veri eşleştirme ölçütlerini belirlemeDiğer bir deyişle, kaynak tablo, karşılık gelen sütunları karşılaştırılır sütunları hedef tabloda belirtin.
Sabit gibi diğer değerlere karşılaştırmaları dahil etmeyin.
Kaynak veya hedef tablodaki satırlara filtre için aşağıdaki yöntemlerden birini kullanın:
Satır uygun OLDUĞUNDA yan tümcesinde süzme arama koşulu belirleyin.Örneğin,WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Kaynak veya hedef Süzülen satırları döndüren bir görünüm tanımlamak ve kaynak veya hedef tablo görünümü başvuru.Görünümün Hedef tabloda tanımlanmışsa, herhangi bir eylem, karşı görünümlerini güncelleştirmek için gerekli koşulları karşılamak gerekir.Bir görünüm kullanarak veri güncelleştirme hakkında daha fazla bilgi için bkz:Bir görünümü kullanarak verileri değiştirme.
Use ile<Genel tablo ifade>yan tümce kaynak veya hedef tablo. satır filtre içinBu yöntem ON yan tümcesinde ek arama ölçütleri belirtmek için benzer ve yanlış sonuçlar doğurabilir.Bu yöntem kullanmaktan kaçının veya uygulamadan önce sınamanız önerilir.
Örnekler
C.Basit bir mektup BİRLEŞTİRMEYİ kullanarak deyim Ekle ve güncelleştir işlemleri için
Varsayalım elinizde birFactBuyingHabits tablo bir veri ambarı veritabanı, her müşterinin son tarih izleyen bir özel ürün. satın İkinci bir tabloSatın alma, bir OLTP, veritabanı kayıtlarını satınalma bir belirli bir hafta. sırasında Belirli müşterileri hiçbir zaman önce gelen alan ürün satırı eklemek istediğiniz her hafta,Satın almaFactBuyingHabits tablosunu. tablo , Zaten satın önce satırları ürünleri satın alan müşteriler, yalnızca satınalma tarih güncelleştirmek istediğinizFactBuyingHabits tablo.Bu ekleme ve güncelleştirme işlemlerini tek bir gerçekleştirilmesi deyim mektup BİRLEŞTİRMEYİ kullanarak.
Aşağıdaki örnek, ilk tablo oluştururPurchasesveFactBuyingHabitsbazı örnek veri. yüklerbirleştirmek deyimleri performansı artırır; bu nedenle, birincil anahtar kısıtlaması oluşturarak kümelenmiş dizinler oluşturulur UNIQUE dizinleri birleştirmek anahtarı oluşturulduğundaProductIDsütunun her iki tablo.
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.
Tabloları artık aşağıdaki verileri ile doldurulur:
dbo.Purchases
ProductID CustomerID 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
ProductID CustomerID 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
Olduğunu iki ürün-müşteri satır için her iki tabloda ortak unutmayın: Önceki hafta yanı sıra, hafta sırasında ürün 707 11794 müşteri satın ve doğru ürün 870 müşteri 15160'ın satın alınmasıyla aynıdır. Bu satırlar için de güncelleştirmeFactBuyingHabits THEN eşleşen yan tümce. WHEN kullanarak bu Satınalmalar Satınalmalar biçiminde kaydedilen tarih Biz tüm diğer satırlara yerleştirinFactBuyingHabits THEN eşleşen yan tümce. WHEN kullanarak NOT
B.GÜNCELLEŞTİR ve Sil işlemlerini gerçekleştirme
Güncelleştirmek için birleştirme aşağıdaki örnekte kullanırProductInventory, tabloAdventureWorksörnek veritabanı günlük, işlenir siparişlerine göreSalesOrderDetailTablo.Aşağıdaki mektup BİRLEŞTİRMEYİ kullanarak deyim,QuantitysütunProductInventoryTablo sayısı her gün için her ürün yer çıkartma ile güncelleştirilirÜrün sayısı 0 olarak bırakın veya aşağıda ürünün stok neden olur, bu ürünün satır silinirProductInventoryTablo.Kaynak tablo üzerinde toplanan NotProductIDsütun.Yoksa bu değil bitti, birden fazlaProductIDkaynak tablo hedef tablo ile eşleşmesi ve birleştirme neden deyim bir hata. geri dönmek için
C.INSERT, UPDATE ve DELETE işlemler yapma
Aşağıdaki örnek, eklemek, güncelleştirmek veya hedef satırları silmek için birleştirme kullanır tablo farklılıkları kaynak verilerle bağlı.Her bir bölüm beş bölümleri olan küçük bir şirkette yönetici göz önünde bulundurun.Şirket, kendi departmanları re-organize karar verir.BİRLEŞTİRME deyim re-organization sonuçlar hedef tablo dbo.Departments uygulamak için aşağıdaki değişiklikler uygulamanız gerekir:
Varolan bazı bölümleri değişmez.
Varolan bazı bölümleri yeni yöneticileri olur.
Yeni oluşturulan bazı bölümleri.
Bazı bölümlerden sonra reorganization bulunmaz.
Şu kod, hedef tablo oluşturur.dbo.Departmentsve yöneticileri ile doldurur
İçin bölümler yapılması için kuruluş değişiklikleri kaynak tablo saklanır. dbo.Departments_delta.Aşağıdaki kod oluşturur ve bu tabloyu doldurur:
Son olarak, hedef tablodaki şirket reorganization yansıtmak için aşağıdaki kod kullanır Kaynak tablosundaki karşılaştırma birleştirme ifadesinedbo.Departments_delta, hedef tablodbo.Departments.Bu karşılaştırma için arama koşulu ON tanımlanan yan tümce ifadesinin.Karşılaştırma sonuçlarına bağlı olarak, aşağıdaki eylemleri alınır.
İki tablo s varolan bölümler, hedef güncelleştirilir tablo yeni adları, yeni yöneticileri veya tablo ikisindeDepartments.Herhangi bir değişiklik varsa, hiçbir şey güncelleştirilir.Bu zaman gerçekleştirilir THEN eşleşen yan tümce.
Tüm bölümlerDepartments_deltaiçinde yok yokDepartmentsiçine eklenenDepartments.Bu gerçekleştirilir WHEN the THEN yan tümcesinin eşleşen değil.
Tüm bölümlerDepartmentsyok Kaynak tablosundakiDepartments_delta gelen silinirDepartments.Bu zaman yapılır değil MATCHED BY yan tümce THEN kaynak.