-
- Üyelik Tarihi
- 24 Mar 2017
-
- Mesajlar
- 4,578
-
- MFC Puanı
- 1,437
SQL Server 2008 versiyonu ile birlikte hayatımıza giren MERGE komutu kullanılarak ayrı ayrı yapılabilen INSERT, UPDATE ve DELETE işlemleri artık tek bir komut ile gerçekleştirilebilmektedir. Özellikle iki tablo arasında veri senkronizasyonu sağlanması için gereken farklı DML işlemleri MERGE komutu ile tek seferde yapılabilmektedir. MERGE komutunun bir başka kullanımı ise veriambarı geliştirme kısmında gerçekleştirilen ETL süreçlerinin bir parçası olan Slowly Changing Dimensions (SCD) yapılarının MERGE kullanılarak tasarlanabilmesidir. Bu konuyu başka bir yazıda ele alacağız. Bu yazımızda SQL Serverda kullanılan MERGE komutunun genel kullanımını ele alacak ve performans optimizasyonu için nelere dikkat edilmesi gerektiği kısaca açıklayacağız.
Kullanılan sistemlerde bazen bir tablodaki verilerin düzenli aralıklarla başka bir tabloya verilerini taşımak ve her iki tabloyu senkron etmek gerekebilir. Bu bazen bir test ortamı ile production ortamı arasında ihtiyaç duyulabilen bir senaryo olabileceği gibi bazen de OLTP veritabanları ile veriambarı katmanı arasında bu gibi ihtiyaçlara gereksinim duyulabilmektedir. Örneğimizde EmployeeSource isminde bir kaynak tablomuzu ve aynı yapıya sahip EmployeeTarget isminde bir hedef tablomuzu kullanacağız. Bu iki tablonun scriptleri aşağıdaki gibidir:
Bu iki tabloyu create ettikten sonra içine örneğimizi gerçekleştireceğimiz birkaç tane veri girişi yapalım:
Ardından bu komutları da çalıştırdıktan sonra tablolarımız üzerindeki verileri kontrol edelim:
Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız (source tablosu ile senkron olmasını istediğimiz) hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaşın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.
Amacımız bu iki tablonun verilerini senkronize etmek olduğundan şu işlemleri yapmamız gerekmektedir;
Yazdığımız kod bloğunu isterseniz kısaca açıklayalım:
İlk olarak MERGE INTO komutu ile hedef tablomuzu belirttik. Ardından verileri hangi tablodan çekeceğimizi USING ifadesi ile belirledik. (Burada USING ifadesi ile sadece var olan tabloları değil view, CTE, derived table, udf vb kullanabilirsiniz.) Kaynak ve hedef tablolarındaki verilerin daha önceden olup olmadıklarını karşılaştırabilmek için unique olan EmployeeID kolonu üzerinden gerekli kontrolü gerçekleştirdik. Bu işlemin ardından;
Kodun son kısmında OUTPUT seçeneği ile etkilenen kayıtlara ait bilgiler çıktı olarak ekranda sonuç setinde gösterilecektir. $action ifadesi MERGE içinde OUTPUT ile birlikte kullanılabilmekte ve yapılan işlemi (INSERT, UPDATE, DELETE) göstermektedir.
İlgili MERGE komutunu çalıştırdığımızda aşağıdaki sonuç karşımıza çıkacaktır:
Buradan da görüleceği üzere EmployeeSource tablosundaki 1, 2 ve 3 nolu kayıtlar UPDATE edilmiş, 5 nolu kayıt (daha önce hedefte olmadığı için) INSERT edilmiş, kaynakta NULL yazan kayıt (aslında hedefte 4 nolu kayıt) artık olmadığı için DELETE edilmiştir.
MERGE komutunu çalıştırdıktan sonra EmployeeSource ve EmployeeTarget tablolarını tekrar sorgulayıp sonucuna bakalım:
Görüldüğü gibi her iki tablodaki kayıtlar artık tamamen birbirine eşit duruma gelmiştir.
Kullanılan sistemlerde bazen bir tablodaki verilerin düzenli aralıklarla başka bir tabloya verilerini taşımak ve her iki tabloyu senkron etmek gerekebilir. Bu bazen bir test ortamı ile production ortamı arasında ihtiyaç duyulabilen bir senaryo olabileceği gibi bazen de OLTP veritabanları ile veriambarı katmanı arasında bu gibi ihtiyaçlara gereksinim duyulabilmektedir. Örneğimizde EmployeeSource isminde bir kaynak tablomuzu ve aynı yapıya sahip EmployeeTarget isminde bir hedef tablomuzu kullanacağız. Bu iki tablonun scriptleri aşağıdaki gibidir:
01create table EmployeeSource
02(
03EmployeeID int,
04FirstName nvarchar(50),
05LastName nvarchar(50),
06Title nvarchar(100),
07RecruitmentDate datetime,
08Salary decimal,
09IsActive bit
10)
11GO
12
13
14create table EmployeeTarget
15(
16EmployeeID int,
17FirstName nvarchar(50),
18LastName nvarchar(50),
19Title nvarchar(100),
20RecruitmentDate datetime,
21Salary decimal,
22IsActive bit
23)
24GO
02(
03EmployeeID int,
04FirstName nvarchar(50),
05LastName nvarchar(50),
06Title nvarchar(100),
07RecruitmentDate datetime,
08Salary decimal,
09IsActive bit
10)
11GO
12
13
14create table EmployeeTarget
15(
16EmployeeID int,
17FirstName nvarchar(50),
18LastName nvarchar(50),
19Title nvarchar(100),
20RecruitmentDate datetime,
21Salary decimal,
22IsActive bit
23)
24GO
Bu iki tabloyu create ettikten sonra içine örneğimizi gerçekleştireceğimiz birkaç tane veri girişi yapalım:
01insert into dbo.EmployeeTarget
02values
03(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1),
04(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
05(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
06(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
07GO
08
09insert into dbo.EmployeeSource
10values
11(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
12(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
13(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
14(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 0)
15GO
02values
03(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 1000, 1),
04(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
05(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
06(4, N'Merve', N'Sağlam', N'Kıdemli Danışman', '20150618', 1800, 1)
07GO
08
09insert into dbo.EmployeeSource
10values
11(1, N'Abdullah', N'Altıntaş', N'Takım Lideri', '20120721', 2000, 1),
12(2, N'İsmail', N'Adar', N'DBA', '20090101', 1500, 1),
13(3, N'Yusuf', N'Boğatepe', N'Danışman', '20140103', 1000, 1),
14(5, N'Şeydanur', N'Sandıkçı', N'Danışman', GETDATE(), 1000, 0)
15GO
Ardından bu komutları da çalıştırdıktan sonra tablolarımız üzerindeki verileri kontrol edelim:
1select * from dbo.EmployeeSource
2select * from dbo.EmployeeTarget
2select * from dbo.EmployeeTarget
Sorguyu çalıştırdığımızda verileri aldığımız kaynak tablomuzu temsil eden EmployeeSource tablosu ile verileri aktarmayı amaçladığımız (source tablosu ile senkron olmasını istediğimiz) hedef tablomuz EmployeeTarget tablosunda bazı verilerin farklı olduğunu görebilirsiniz. Örneğimizde 1 nolu id ye sahip Abdullah Altıntaşın kaynak tablodaki maaş bilgisi değişmiş ve 1000 yerine 2000 değerini almış, 2 ve 3 nolu id ye ait kayıtlarda herhangi bir değişiklik yapılmamıştır. Ayrıca kaynak tabloda hedef tablosunda henüz bulunmayan 5 nolu id ye sahip Şeydanur Sandıkçı eklenmiş olmakla beraber hedef tablosunda artık kaynak tabloda bulunmayan 4 nolu id ye sahip Merve Sağlam kaydı bulunmaktadır.
Amacımız bu iki tablonun verilerini senkronize etmek olduğundan şu işlemleri yapmamız gerekmektedir;
- Kaynak tabloda olup hedef tabloda olmayan verileri insert etmek,
- Kaynak tabloda olup hedef tabloda da olan verileri olması muhtemel değişiklikleri uygulamak için update etmek,
- Hedef tabloda olup artık kaynak tabloda bulunmayan kayıtları delete etmek.
01MERGE INTO dbo.EmployeeTarget as t
02USING dbo.EmployeeSource as s
03ON t.EmployeeID = s.EmployeeID
04WHEN MATCHED THEN
05 UPDATE SET t.FirstName = s.FirstName,
06 t.LastName = s.LastName,
07 t.Title = s.Title,
08 t.RecruitmentDate = s.RecruitmentDate,
09 t.Salary = s.Salary,
10 t.IsActive = s.IsActive
11WHEN NOT MATCHED BY TARGET THEN
12 INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
13 VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, s.IsActive)
14WHEN NOT MATCHED BY SOURCE THEN
15 DELETE
16OUTPUT $action as YapilanIslem, deleted.EmployeeID, inserted.EmployeeID;
02USING dbo.EmployeeSource as s
03ON t.EmployeeID = s.EmployeeID
04WHEN MATCHED THEN
05 UPDATE SET t.FirstName = s.FirstName,
06 t.LastName = s.LastName,
07 t.Title = s.Title,
08 t.RecruitmentDate = s.RecruitmentDate,
09 t.Salary = s.Salary,
10 t.IsActive = s.IsActive
11WHEN NOT MATCHED BY TARGET THEN
12 INSERT (EmployeeID, FirstName, LastName, Title, RecruitmentDate, Salary, IsActive)
13 VALUES (s.EmployeeID, s.FirstName, s.LastName, s.Title, s.RecruitmentDate, s.Salary, s.IsActive)
14WHEN NOT MATCHED BY SOURCE THEN
15 DELETE
16OUTPUT $action as YapilanIslem, deleted.EmployeeID, inserted.EmployeeID;
Yazdığımız kod bloğunu isterseniz kısaca açıklayalım:
İlk olarak MERGE INTO komutu ile hedef tablomuzu belirttik. Ardından verileri hangi tablodan çekeceğimizi USING ifadesi ile belirledik. (Burada USING ifadesi ile sadece var olan tabloları değil view, CTE, derived table, udf vb kullanabilirsiniz.) Kaynak ve hedef tablolarındaki verilerin daha önceden olup olmadıklarını karşılaştırabilmek için unique olan EmployeeID kolonu üzerinden gerekli kontrolü gerçekleştirdik. Bu işlemin ardından;
- WHEN MATCHED THEN ifadesi ile hedefte daha önce bulunan ve kaynak tablosundaki muhtemel değişiklikleri uygulamamız için gerekli olan UPDATE komutu ile güncelleme yapılması,
- WHEN NOT MATCHED BY TARGET ifadesi ile hedefte henüz bulunmayan ama kaynak tablosunda bulunan ve hedef tablosuna eklenmesi gereken kayıtlar için INSERT komutu ile gerekli kayıtların eklenmesi,
- WHEN NOT MATCHED BY SOURCE ifadesi ile hedefte bulunan ancak kaynak tablosunda artık bulunmayan kayıtların silinmesi için (test verileri vb olabilir) DELETE komutu ile bu verilerin silinmesi sağlanmıştır.
Kodun son kısmında OUTPUT seçeneği ile etkilenen kayıtlara ait bilgiler çıktı olarak ekranda sonuç setinde gösterilecektir. $action ifadesi MERGE içinde OUTPUT ile birlikte kullanılabilmekte ve yapılan işlemi (INSERT, UPDATE, DELETE) göstermektedir.
İlgili MERGE komutunu çalıştırdığımızda aşağıdaki sonuç karşımıza çıkacaktır:
Buradan da görüleceği üzere EmployeeSource tablosundaki 1, 2 ve 3 nolu kayıtlar UPDATE edilmiş, 5 nolu kayıt (daha önce hedefte olmadığı için) INSERT edilmiş, kaynakta NULL yazan kayıt (aslında hedefte 4 nolu kayıt) artık olmadığı için DELETE edilmiştir.
MERGE komutunu çalıştırdıktan sonra EmployeeSource ve EmployeeTarget tablolarını tekrar sorgulayıp sonucuna bakalım:
Görüldüğü gibi her iki tablodaki kayıtlar artık tamamen birbirine eşit duruma gelmiştir.
- MERGE Performans İçin Dikkat Edilecek Noktalar
- Kaynak ve hedef tabloları joinleyeceğimiz kolonlar üzerinde uygun indexlerin oluşturulması,
- Eğer verileri üzerinde bir filtre verilmek isteniyorsa bunun ON ifadesinde değil uygun eşleştirme kısmında (WHEN MATCHED ya da NOT MATCHED) ele alınması,
- Bir veri dosyasından OPENROWSET ile veri çekilirken uygun indexlerin oluşturulmasının yanı sıra OPENROWSET ifadesi ile birlikte ORDER ve UNIQUE bileşenlerinin verilmesi performansa olumlu katkı yapacaktır.