Insert ve Delete İşlemlerinde OUTPUT Cümleciğinin Kullanımı

Output cümleciği  SQL Server 2005 ile birlikte kullanıma sunulan bir ifadedir. INSERT, UPDATE ve DELETE işlemlerinden etkilenen her bir satıra ait değeri çıktı olarak göstermeye yarar. Bu cümleciğin MERGE komutu ile birlikte kullanıma başlanması ise SQL Server 2008 ile birlikte olmuştur.

Output cümleciğinden dönen sonuçlar ayrı bir tabloya insert edilebilir. Bu cümlecik yaygın olarak kayıtların denetimini sağlamak için kullanılır. Bu nedenle değişen datanın yakalanması işlemlerinde de kullanımı mümkündür. Bu yazımda sizlere Insert ve Delete işlemlerinden etkilenen satırlardaki çıktıların OUTPUT cümleciği ile nasıl yakalanabileceğini gösteriyor olacağım.

İlk olarak örneğimizi gerçekleştireceğimiz Customer adındaki tabloyu oluşturabilmek için aşağıdaki T-SQL komutunu çalıştırıyoruz.

IF OBJECT_ID ('Customer', 'U') IS NOT NULL
DROP TABLE dbo.Customer;

CREATE TABLE [dbo].[Customer](
       [CustomerID] [int] IDENTITY(1,1) NOT NULL,
       [FirstName] varchar(50)  NOT NULL,
       [LastName] varchar(50) NOT NULL,
       [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Sorgumuz başarılı bir şekilde çalıştıktan sonra bu tablomuza aşağıdaki sorgu yardımıyla tek satırlık bir kayıt ekliyoruz.

Insert into [dbo].[Customer]([FirstName],[LastName],[ModifiedDate])
 Values('Ali','Veli',getdate());

OUTPUT kullanımı bize 2 sanal tablo oluşturur. Bunlar:

1. “INSERTED”: INSERT ve UPDATE sonucu oluşan yeni satırları kapsar,
2. “DELETED”: Silinen eski kayıtları kapsar (INSERT işlemi için boştur).

Şimdi Insert ifadesinde OUTPUT cümleciğinin nasıl kullanıldığına bakalım.

Bir tabloya kayıt eklerken Insert ifadesi kullandığımızda, sorguyu çalıştırdıktan sonra Messages kısmında sadece komutun başarılı veya başarısız çalıştığına dair bir çıktı görürüz. Insert ifadesi ile birlikte OUTPUT kullanımı ise bize insert yapılan kayıtların neler olduğunu göstermektedir. Bu işlemi gerçekleştirebilmek adına aşağıdaki sorgu yardımıyla Customer tabloma bir yeni kayıt ekliyorum.

 Insert into [dbo].[Customer]([FirstName],[LastName],[ModifiedDate])
 Output inserted.FirstName, inserted.LastName, inserted.ModifiedDate
 Values('Ayse','Fatma',getdate());

Sorgu çalıştıktan sonra gördüğünüz gibi çıktı ekranında eklemiş olduğum kaydın ne olduğunu görmüş olduk. Aynı zamanda Customer tabloma da insert işlemi gerçekleşmiş oldu.

Şimdi bu cümleciği Değişken Tablo’larda nasıl kullanabiliriz ona göz atalım.

Aşağıdaki sorgu yardımıyla @inserted adında bir değişken tablo tanımlayıp bu tabloya yeni bir kayıt ekleyerek son satırdaki select sorgusuyla bu tablomuzu sorguluyoruz.

 Declare @inserted table ([FirstName] varchar(50),[LastName] varchar(50), [ModifiedDate] datetime)

 Insert into [dbo].[Customer]([FirstName],[LastName],[ModifiedDate])
 Output inserted.FirstName, inserted.LastName, inserted.ModifiedDate
 Into @inserted
 Values('Ayse','Fatma',getdate());

 select * from @inserted

Gördüğünüz gibi oluşturduğumuz değişken tabloya kayıt eklerken de bu kaydın ne olduğunu OUTPUT cümleciği yardımıyla görebildik.

Peki identity bir kolon insert cümlesinde yer almasa bile OUTPUT cümleciği kullanıldığında nasıl bir sonuç üretir?

Aşağıdaki sorgu yardımıyla yine bir değişken tablo oluşturup bu tabloya yeni bir kayıt ekliyoruz. Fakat bu sefer OUTPUT cümleciğinden sonra inserted.CustomerID diyerek çıktı ekranında bu alanı da görmek istediğimi belirtiyorum. Daha sonra bu tabloya select çektiğim zaman aşağıdaki ekranda da gördüğünüz gibi CustomerID kolonumuzda çıktı ekranında identity sırasında aldığı değer ile birlikte gelmiş oldu.

 Declare @inserted table ([CustomerID] int,[FirstName] varchar(50),[LastName] varchar(50), [ModifiedDate] datetime)

 Insert into [dbo].[Customer]([FirstName],[LastName],[ModifiedDate])
 Output inserted.CustomerID, inserted.FirstName, inserted.LastName, inserted.ModifiedDate
 Into @inserted
 Values('Ayse','Fatma',getdate());

 select * from @inserted

OUTPUT cümleciğinin Insert işlemlerinde kullanımı bu şekildeydi. Şimdi gelelim Delete işlemlerinde bu cümleciğin nasıl kullanıldığına.

Bu örnek için de yukarıda oluşturmuş olduğum Customer tablomu kullanıyorum. Bu tablomda olduğunu bildiğim 3 numaralı CustomerID’ye ait değeri OUTPUT cümleceğini kullanarak aşağıdaki sorgu yardımıyla siliyorum.

 delete from [dbo].[Customer]
 output deleted.CustomerID, deleted.FirstName, deleted.LastName, deleted.ModifiedDate
 where CustomerID = 3

Yukarıdaki resimde görüldüğü gibi 3 numaralı CustomerID’ye ait değer Customer tablomdan silindiği gibi aynı zamanda çıktı ekranında bu kayda ait bilgileri de görmüş oldum.

Insert işleminde olduğu gibi Delete işleminde de Değişken Tablo oluşturarak OUTPUT cümleciğini kullanmak mümkün.

Aşağıdaki sorguyu kullanarak bu örneğimde öncelikle @deleted adında değişken bir tablo oluşturuyorum. Daha sonra silmek istediğim tabloyu belirterek ilk OUTPUT cümleciğinde silinecek kaydın çıktı ekranında görülmesini sağlarken, ikinci OUTPUT sorgumda ise CustomerID’si 1 olan kaydın @deleted isimli değişken tabloya yeni bir kayıt olarak eklenmesini sağlıyorum. Daha sonra ise select sorgusu ile birlikte @deleted isimli tablomu sorguluyorum.

 Declare @deleted table ([CustomerID] int,[FirstName] varchar(50),[LastName] varchar(50), [ModifiedDate] datetime)

 delete from [dbo].[Customer]    
 Output deleted.CustomerID, deleted.FirstName, deleted.LastName, deleted.ModifiedDate
 Into @deleted
 output deleted.CustomerID, deleted.FirstName, deleted.LastName, deleted.ModifiedDate
 where CustomerID = 1

 select * from @deleted

Çıktı ekranı beklenildiği gibi 2 ayrı sonuç döndürüyor. Üsteki kayıt OUTPUT cümleciğinin özelliğinden dolayı silinen kaydın ne olduğunu bize gösterirken, ikinci kayıt ise @deleted isimli değişken tablomuza select çektiğimiz için bu tabloya insert ettiğimiz 1 numaralı CustomerID’ye ait kaydı bize göstermektedir.

Yazımı bitirmeden önce son olarak OUTPUT cümleciğinin limitlerinden bahsetmek gerekiyor. Bu limitleri aşağıdaki gibi sıralamak mümkün:

1. Local partitioned view’ları, distributed partitioned view’ları, veya remote tabloları referans alan DML ifadelerinde kullanılamaz.
2. EXECUTE ifadesi içeren INSERT cümleciklerinde kullanılamaz.
3. Eğer veritabanı uyumluluk düzeyi (database compatibility level) 100 olarak ayarlanmışsa OUTPUT cümleciklerindeki Full-Text koşullarına izin verilmez.
4. OUTPUT INTO cümleciği, view’lara kayıt eklemek için veya rowset fonksiyonlarında kullanılamaz.
5. Kullanıcı tanımlı fonksiyonlar hedef olarak bir tabloya sahip OUTPUT INTO cümleciği içeriyorsa oluşturulamaz.

OUTPUT cümleciğiyle ilgili detaylı bilgiye bu linkten erişmek mümkün. Sonraki yazılarda görüşmek dileğiyle.