JorisR.be

JorisR.be

Code-Run-Beer-Repeat
15-3-2018

Output in SQL Server

Het output statement binnen SQL server laat toe om bij een insert/update/delete de waardes die gewijzigd zijn op te vragen en deze te hergebruiken binnen onze query.  Deze data kan je dan bijvoorbeeld gebruiken om nog een andere tabel op te vullen.
In deze post bekijken we een paar voorbeelden.

Als we in SQL server insert/update/delete statements schrijven, en we willen de weggeschreven data nog hergebruiken (bijvoorbeeld om weg te scrhijven in een andere tabel) kunnen we gebruik maken van het "Output" statement.

In het eerste voorbeeld hebben we een tabel "Beer".  Nu willen we deze tabel gebruiken om data in een andere tabel "BeerCopy" te krijgen.  De ID van "BeerCopy" is autonummering, we hebben dus geen zicht welke Id een bepaalde rij gaat krijgen. Nadien willen we de nieuw aangemaakte rijen in een tabel variabele krijgen om te tonen als output.
Om dit optimaal te doen gebruik je "Output":

declare @OutputTable table(id bigint, name varchar(60))

insert into BeerCopy(BeerName) 
output inserted.Id, inserted.BeerName
into @OutputTable(Id, name)
select Name
from Beer
where Cat_Id = 1

select * from @OutputTable

Het eerste deel is de output table declareren, nadien doen we een gewone 'Insert'.
Maar tussen het Insert statement en het definiëren welke waarden we willen toevoegen komt nog een extra statement: "Output" waar we de waarden opsommen die we willen gebruiken.  Met het "Into" statement voegen we deze waarden toe aan onze tijdelijke tabel.

Merge

Als we nu niet enkel data willen hergebruiken maar ook data van bijvoorbeeld de query zelf is dit niet mogelijk met alleen een "Output".  Een oplossing hiervoor is dit te combineren met "Merge".

Voor het volgend voorbeeld gaan we verder op het vorige voorbeeld. Enkel nu willen we een mapping tabel bijhouden tussen de Id's van de oude tabel en de nieuw toegevoegde Id's.

 

Merge BeerCopy
using (select Id, Name
from Beer
where Cat_Id = 1) as source
ON     (1=0)
WHEN   NOT MATCHED THEN
INSERT (BeerName) VALUES  (source.Name)
OUTPUT [source].Id,  INSERTED.Id
into @OutputTable (oldId, newId);

select * from @OutputTable

De Merge combineerd de 'Insert' en het select statement.
Met de (1=0) gaan we ervoor zorgen dat de code na "WHEN NOT MATCHED" uitgevoerd wordt.

Leave A Comment