Narcis Miclaus

8 min de citit

SQL Server, de la zero Lecția 17 / 40

INSERT, UPDATE, DELETE și de ce MERGE e periculos

Cele patru verbe DML, pattern-urile UPSERT care chiar funcționează, clauza OUTPUT și lista lungă de motive pentru a evita MERGE.

DML — Data Manipulation Language — e cealaltă jumătate a SQL. Până acum am tot citit. Acum scriem. INSERT, UPDATE, DELETE și controversatul MERGE. Plus clauza OUTPUT, care transformă pe oricare dintre ele într-o operațiune de tipul „spune-mi ce ai modificat”.

Runehold scrie date în fiecare secundă din fiecare zi. Înregistrări de clienți noi, comenzi noi, ajustări de stoc, actualizări de status pentru livrări, retururi, refund-uri. Cum se face asta — atomic, în siguranță și cu trail-ul de audit potrivit — e subiectul de azi.

INSERT

Un singur rând

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES (N'Maria Rossi', N'maria@example.it', 'IT');

Listă de coloane explicită, clauză VALUES explicită. Poți omite lista de coloane pentru a face insert pe poziție (corespunde definiției tabelei), dar e fragil — adăugarea unei coloane mai târziu strică în tăcere fiecare query. Listează întotdeauna coloanele.

Mai multe rânduri

INSERT INTO Sales.Customer (Name, Email, Country)
VALUES
    (N'Maria Rossi',   N'maria@example.it',  'IT'),
    (N'Hans Schmidt',  N'hans@example.de',   'DE'),
    (N'Liesbeth Jong', N'liesbeth@nl.com',   'NL');

O singură instrucțiune, trei rânduri. Mai rapid decât trei INSERT-uri separate, pentru că SQL Server îl loghează ca pe o singură operațiune.

Insert dintr-un SELECT

-- Copiază clienții într-o tabelă de backup
INSERT INTO Sales.Customer_Backup (CustomerId, Name, Email, Country, BackedUpAt)
SELECT CustomerId, Name, Email, Country, SYSUTCDATETIME()
FROM Sales.Customer;

-- Migrează date de staging în producție
INSERT INTO Sales.Customer (Name, Email, Country)
SELECT Name, Email, Country
FROM Staging.CustomerImport
WHERE IsValid = 1;

Cel mai comun pattern de bulk-loading. Fii explicit cu coloanele din ambele părți.

Obține identity-ul rândului inserat

-- Vechea metodă: SCOPE_IDENTITY()
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
VALUES (@cust, @total, @country, @vat);
DECLARE @newId BIGINT = SCOPE_IDENTITY();

-- Metoda modernă: OUTPUT
INSERT INTO Sales.Orders (CustomerId, Total, CountryCode, VatRate)
OUTPUT INSERTED.OrderId, INSERTED.OrderDate
VALUES (@cust, @total, @country, @vat);

OUTPUT INSERTED.* întoarce rândurile abia inserate. Funcționează și pentru bulk inserts — primești câte un rând de output per rând inserat. Util pentru a întoarce ID-urile noi către aplicație, sau pentru a scrie într-o tabelă de audit în aceeași instrucțiune.

UPDATE

Update simplu

UPDATE Sales.Customer
SET    Email = N'maria.new@example.it',
       UpdatedAt = SYSUTCDATETIME(),
       UpdatedBy = SUSER_SNAME()
WHERE  CustomerId = 12345;

Pune întotdeauna o clauză WHERE. Dacă o uiți, actualizezi fiecare rând din tabelă. E cea mai frecventă greșeală dezastruoasă din SQL. Înainte de a rula orice UPDATE sau DELETE, fac două lucruri:

  1. Copiez instrucțiunea, înlocuiesc UPDATE ... SET ... cu SELECT * și verific că WHERE-ul întoarce rândurile pe care le aștept.
  2. Înfășor instrucțiunea reală în BEGIN TRAN; ...; ROLLBACK;, inspectez numărul de rânduri și abia când sunt sigur fac commit.

Obiceiurile paranoice salvează cariere.

Update cu JOIN

-- Actualizează codul de țară pe comenzi pe baza țării clientului
UPDATE o
SET o.CountryCode = c.Country
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.CountryCode IS NULL;

Sintaxă ciudată, specifică SQL Server: UPDATE <alias> urmat de SET și apoi FROM. E standardul de facto pentru T-SQL și nimeni nu pune întrebări la code review.

OUTPUT pe UPDATE

Același pattern ca la INSERT:

UPDATE Sales.Orders
SET Status = 2
OUTPUT INSERTED.OrderId, INSERTED.Status AS new_status, DELETED.Status AS old_status
WHERE Status = 1 AND OrderDate < DATEADD(DAY, -7, GETDATE());

INSERTED conține rândul după update, DELETED îl conține înainte. Perfect pentru jurnale de audit: „cine a modificat ce, din ce în ce”.

DELETE

DELETE FROM Sales.Orders
WHERE Status = 9 AND OrderDate < DATEADD(YEAR, -7, GETDATE());

Aceeași avertizare: clauză WHERE, altfel ștergi tot.

TRUNCATE TABLE e mai rapid decât DELETE fără WHERE, dar renunță temporar la relațiile de tip foreign key (am acoperit asta în lecția 5). TRUNCATE dacă vrei toate rândurile dispărute și tabela goală; DELETE ... WHERE pentru orice altceva.

DELETE cu JOIN

-- Șterge comenzile al căror client nu mai există (dacă FK-ul permite)
DELETE o
FROM Sales.Orders AS o
LEFT JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

Aceeași sintaxă în două părți ca la UPDATE. Funcționează curat odată ce te obișnuiești.

OUTPUT pe DELETE

-- Arhivează comenzile înainte de ștergere
DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < DATEADD(YEAR, -7, GETDATE());

OUTPUT ... INTO <table> redirecționează output-ul direct într-o altă tabelă. Delete + arhivare atomic, într-o singură instrucțiune. Pentru workflow-uri de retenție a datelor, pattern-ul ăsta e aur curat.

Problema UPSERT

„Inserează dacă nu există, altfel actualizează.” Fiecare aplicație are nevoie de asta. Soluția simplă pare greșită, dar e de fapt cea corectă:

-- Pattern-ul „IF EXISTS", sigur și lizibil
IF EXISTS (SELECT 1 FROM Sales.Customer WHERE Email = @email)
BEGIN
    UPDATE Sales.Customer
    SET    Name = @name, UpdatedAt = SYSUTCDATETIME()
    WHERE  Email = @email;
END
ELSE
BEGIN
    INSERT INTO Sales.Customer (Name, Email, Country)
    VALUES (@name, @email, @country);
END;

Simplu, explicit, sigur. Înfășoară-l într-o tranzacție și gata.

Problemă: la insert-uri concurente, două sesiuni ar putea trece amândouă verificarea IF EXISTS și amândouă să încerce INSERT, una primind o violare de constrângere unique. Pentru tabele cu trafic redus, e ok. Pentru tabele „fierbinți”, adaugă HOLDLOCK:

BEGIN TRAN;

    IF EXISTS (SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Email = @email)
        UPDATE Sales.Customer SET ... WHERE Email = @email;
    ELSE
        INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);

COMMIT;

UPDLOCK, HOLDLOCK se asigură că nimeni nu se strecoară între verificarea EXISTS și insert. Vom acoperi blocările ca lumea în lecția 20.

Alternativă: lasă constrângerea unique să facă treaba și prinde eroarea:

BEGIN TRY
    INSERT INTO Sales.Customer (Name, Email, Country) VALUES (@name, @email, @country);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627   -- violare de constrângere unique
        UPDATE Sales.Customer SET Name = @name WHERE Email = @email;
    ELSE
        THROW;
END CATCH;

Optimist: încearcă insert mai întâi, dă-te înapoi spre update dacă apare conflict. Bun pentru tabele unde coliziunile sunt rare.

MERGE: de ce e periculos

MERGE încearcă să facă UPSERT într-o singură instrucțiune:

MERGE INTO Sales.Customer AS target
USING (SELECT @email AS Email, @name AS Name, @country AS Country) AS src
ON target.Email = src.Email
WHEN MATCHED THEN
    UPDATE SET Name = src.Name, UpdatedAt = SYSUTCDATETIME()
WHEN NOT MATCHED THEN
    INSERT (Name, Email, Country) VALUES (src.Name, src.Email, src.Country);

Elegant, concis, nerecomandat.

Lista de motive pentru a evita MERGE în SQL Server:

  • Multiple bug-uri documentate în care MERGE întoarce rezultate greșite sau nu face nimic, în tăcere. Aaron Bertrand ține o listă de 15 ani. Unele sunt rezolvate; altele persistă.
  • Probleme de concurență mai grave decât la pattern-ul IF EXISTS + INSERT/UPDATE.
  • Interacționează prost cu indici filtrați, view-uri indexate și trigger-e INSTEAD OF.
  • Clauza WHEN NOT MATCHED BY SOURCE poate șterge tăcut mai mult decât te aștepți.
  • Mai greu de citit și debug decât pattern-ul explicit IF/ELSE.

Chiar și ghidul Microsoft a redus discret accentul pus pe MERGE. Dacă îl vezi în cod existent, înțelege ce face, dar nu apela la el în cod nou. Pattern-urile sigure sunt IF EXISTS + INSERT/UPDATE sau INSERT + try/catch.

Dacă chiar trebuie să folosești MERGE (poate pentru un script ETL one-time), citește mai întâi lista existentă de bug-uri. Nu glumesc.

Update-uri în loturi pentru tabele mari

Rularea UPDATE Orders SET Status = 9 WHERE OrderDate < ... pe o tabelă cu 200 de milioane de rânduri care se potrivesc va bloca tabela, va umple log-ul și va face fiecare utilizator să aștepte.

Sparge în loturi:

WHILE 1 = 1
BEGIN
    UPDATE TOP (10000) Sales.Orders
    SET Status = 9
    WHERE Status = 1 AND OrderDate < '2020-01-01';

    IF @@ROWCOUNT = 0 BREAK;

    -- Lasă alte query-uri să respire
    WAITFOR DELAY '00:00:00.100';
END;

10.000 de rânduri pe lot, pauză de 100ms. Tranzacții mici, vârfuri mici de log, fără escaladare la blocare la nivel de tabelă. Același pattern pentru delete-uri mari.

Pentru ștergeri-de-aproape-tot, altă abordare: creezi o tabelă nouă cu rândurile pe care le vrei, faci drop la cea veche, redenumești cea nouă. Adesea mai rapid decât ștergerea rând cu rând.

Clauza OUTPUT pe larg

Sinteza utilizărilor OUTPUT:

-- Întoarce modificările către client
INSERT INTO ... OUTPUT INSERTED.Id ... VALUES ...;
UPDATE ...    OUTPUT DELETED.OldVal, INSERTED.NewVal WHERE ...;
DELETE FROM ... OUTPUT DELETED.* WHERE ...;

-- Redirecționează modificările într-o tabelă (pentru audit, arhivă etc.)
UPDATE ...
OUTPUT DELETED.*, INSERTED.*, SUSER_SNAME() AS changed_by, SYSUTCDATETIME() AS changed_at
INTO Audit.CustomerChanges
WHERE ...;

Tot ce vezi în DELETED. și INSERTED. e accesibil. Caz comun de utilizare: o tabelă de audit care surprinde înainte/după pentru fiecare modificare, scrisă de aceeași instrucțiune care a făcut modificarea.

Rulează asta pe mașina ta

USE Runehold;
GO

-- 1. INSERT de bază cu mai multe rânduri
INSERT INTO Sales.Customer (Name, Country)
VALUES
    (N'Elena García', 'ES'),
    (N'Pierre Dupont', 'FR'),
    (N'Lars Hansen',  'SE');

-- 2. UPDATE cu JOIN + OUTPUT
UPDATE o
SET o.Notes = CONCAT(o.Notes, N'[' + c.Country + N']')
OUTPUT INSERTED.OrderId, DELETED.Notes AS old_notes, INSERTED.Notes AS new_notes
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.Notes IS NULL OR o.Notes NOT LIKE N'%[%]%';

-- 3. DELETE cu OUTPUT către o tabelă de arhivă
IF OBJECT_ID('Sales.Orders_Archive', 'U') IS NULL
BEGIN
    SELECT TOP 0 * INTO Sales.Orders_Archive FROM Sales.Orders;
END;

DELETE FROM Sales.Orders
OUTPUT DELETED.*
INTO Sales.Orders_Archive
WHERE OrderDate < '2026-01-01';

SELECT COUNT(*) FROM Sales.Orders_Archive;

-- 4. UPSERT pe calea sigură, înfășurat în tranzacție
DECLARE @email NVARCHAR(200) = N'newcomer@example.com';
DECLARE @name  NVARCHAR(100) = N'Newcomer One';
DECLARE @country CHAR(2)     = 'PT';

BEGIN TRAN;
    IF EXISTS (
        SELECT 1 FROM Sales.Customer WITH (UPDLOCK, HOLDLOCK) WHERE Name = @name
    )
        UPDATE Sales.Customer
        SET Country = @country
        WHERE Name = @name;
    ELSE
        INSERT INTO Sales.Customer (Name, Country) VALUES (@name, @country);
COMMIT;

-- 5. Pattern de update în loturi (rulează o dată, datele sunt mici)
WHILE 1 = 1
BEGIN
    UPDATE TOP (100) Sales.Orders
    SET Notes = COALESCE(Notes, N'') + N' '
    WHERE Notes IS NULL OR Notes NOT LIKE '% ';

    IF @@ROWCOUNT = 0 BREAK;
END;

Fiecare instrucțiune DML ar trebui să fie ceva ce ai rula în fața echipei tale. „O să actualizez înregistrarea fiecăruia” nu e niciodată scopul; „o să actualizez cele 47 de rânduri care se potrivesc cu acest filtru” este. Cunoaște întotdeauna numărul de rânduri înainte de a face commit.

Următoarea lecție: tranzacții și ACID. Ce înseamnă cu adevărat „atomic” când serverul cade la mijlocul unui commit și de ce obiceiul tău cu BEGIN TRAN + COMMIT e cel mai bun prieten al DBA-ului.

Caută