Wyobraźmy sobie następujący scenariusz. Mamy jakąś bazę danych i chcemy do niej wstawić gotowe rekordy lub zaktualizować już istniejące. Załóżmy, że chcemy zaktualizować tabelę User

i wstawić testowe wartości do kolumn Name i Surname. Nasz zbiór danych testowych wygląda następująco: 

Chcemy wstawić te dane do istniejącej tabeli. Jak możemy to zrobić?

Zwykły skrypt

Możemy napisać program, który nam wygeneruje listę skryptów podmieniając kolejno parametry dla pojedynczego rekordu:

UPDATE User SET Name = '{username}', Surname = '{surname}' WHERE Id = {id};

Cały skrypt wyglądałby następująco:

UPDATE User SET Name = 'TestName1', Surname = 'TestSurname1' WHERE Id = 1;
UPDATE User SET Name = 'TestName2', Surname = 'TestSurname2' WHERE Id = 2;

Oczywiście dla tak prostego przykładu jak najbardziej można użyć to rozwiązanie. Jednakże jeśli pojedynczy skrypt byłby bardziej rozbudowany (np. zawierałby JOINY do kilku innych tabel), to jego czytelność drastycznie by zmalała. Każdy UPDATE to byłoby x takich samych, powtarzanych linijek skryptu. A po co się powtarzać, gdy można to zrobić inaczej?

Użycie CTE i magicznego słówka WITH

Z pomocą przychodzą wspólne wyrażenia tablicowe (CTE – Common Table Expressions) i magiczne słówko WITH. Pozwala ono utworzyć zbiór danych jako swojego rodzaju tabelę pośrednią. Wygląda to następująco:

WITH Source(Id, UserName, UserSurname) AS
(
    SELECT 1, 'TestName1', 'TestSurname1'
    UNION ALL
    SELECT 2, 'TestName2'', 'TestSurname2'
)
UPDATE TableName SET Name = Source.UserName, Surname = Source.UserSurname
WHERE Id = Source.Id

Jak widać, na początku zdefiniowaliśmy Source jako źródło danych. Następnie napisaliśmy jednorazowo skrypt UPDATE, który wywoła się dla każdego rekordu w SOURCE.

Podsumowanie

Jak widać, w przypadku bardziej skomplikowanych skryptów, użycie CTE może w znaczący sposób zmniejszyć liczbę linijek skryptu i zwiększyć jego czytelność. Skrypt jest podzielony na część definiującą dane wejściowe i część wykonującą konkretne zapytanie.