INSERT over DML ist eine Vereinfachung im Gebrauch der in SQL Server 2005 eingeführten OUTPUT Klausel, die es zusätzlich erlaubt Daten zu filtern.

Zur Erinnerung: Die OUTPUT Klausel gibt die einem DML Statement (UPDATE, INSERT, DELETE, MERGE) Einträge der systemeigenen Tabellen inserted und deleted zurück (Besonderheiten bei MERGE im nächsten Artikel). Diese können direkt an den Datenkonsumenten zurückgegeben werden oder in eine Variable vom Typ Table geschrieben werden, um dort weiterverarbeitet zu werden, beispielsweise um Logs zu schreiben.

INSERT over DML kürzt den letzteren Weg ab. Statt die Daten in eine Variable vom Typ Table zu schreiben und von dort aus in eine Logging Tabelle, kann mittels INSERT over DML direkt in die Logging Tabelle geschrieben werden. Zusätzlich können die Daten mit WHERE gefiltert werden. Dies ist eine atomare Operation.

Zuerst einmal Testdaten erzeugen

use tempdb

create table dbo.products
(
    id      int,
    price   money
)

create table dbo.prodlog
(
    id      int,
    diff    money,
    
)

declare @prodlog table
(
    id      int,
    diff    money
)

insert into dbo.products values
(1, 1),
(2, 1.23),
(3, 2.05),
(4, 1.99),
(5, .5)

--Die Transaktion dient dazu, die Daten wieder im Ursprungszustand wiederherzustellen
begin tran

--SQL Server 2005 Vorgehensweise
update dbo.products set
    price *= 1.25 
output
     inserted.id id
    ,inserted.price - deleted.price diff
into @prodlog

insert into dbo.prodlog
select 
     id
    ,diff
from @prodlog
where
    diff > .5

select * from dbo.prodlog

rollback

--SQL Server 2008 Vorgehensweise
insert into dbo.prodlog
select
      id
     ,diff
from
(
    update dbo.products set
        price *= 1.25 
    output
         inserted.id id
        ,inserted.price - deleted.price diff
) U
where
    diff > .5

 
select * from dbo.prodlog

--Aufräumen
drop table 
     dbo.products
    ,dbo.prodlog

Hieraus lassen sich gedanklich die interessantesten Anwendungsmöglichkeiten konstruieren, aber leider erlauben die Einschränkungen, außer den Anwendungsfälle des Loggings/Auditing/Change Managements sowie die vereinfachte Bearbeitung von „Slowly changing dimensions“ im Zusammenhang mit MERGE als BI Thema, eigentlich nichts. (Da diese Einschränkungen sehr umfangreich sind, bitte in den BOL nachschlagen).

Der nächsten Artikel handelt von MERGE.