Introducere în Declarația MERGE și modificarea datelor SQL Server

Instrucțiunea MERGE este utilizată pentru a face modificări într-un singur tabel pe baza valorilor potrivite din anther. Poate fi folosit pentru a combina operațiile de inserare, actualizare și ștergere într-o singură instrucțiune. În acest articol, vom explora cum să utilizați declarația MERGE. Discutăm despre cele mai bune practici, limitări și rezolvări cu mai multe exemple.

Acesta este al cincilea articol dintr-o serie de articole. Puteți începe la început citind Introducere în instrucțiunile de modificare a datelor SQL Server.

Toate exemplele pentru această lecție se bazează pe Microsoft SQL Server Management Studio și baza de date AdventureWorks2012. Puteți începe să utilizați aceste instrumente gratuite folosind Ghidul meu Introducere în utilizarea SQL Server

Înainte de a începe

Deși acest articol folosește baza de date AdventureWorks pentru exemplele sale, am decis să creez mai multe exemple de tabele pentru utilizare în baza de date pentru a ajuta la o mai bună ilustrare a conceptelor acoperite. Aici puteți găsi scriptul pe care va trebui să îl rulați. Observați că există o secțiune specială referitoare la MERGE.

Structura de bază

Instrucțiunea MERGE combină operațiile INSERT, DELETE și UPDATE într-un singur tabel. Odată ce ați înțeles cum funcționează, veți vedea că simplifică procedura, utilizând toate cele trei instrucțiuni separat pentru a sincroniza datele.

Mai jos este un format generalizat pentru instrucțiunea de îmbinare.

MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement

Instrucțiunea merge funcționează utilizând două tabele, sourceTable și targetTable. Tabelul țintă este tabelul care trebuie modificat pe baza datelor conținute în tabelul sursă.

Cele două tabele sunt comparate utilizând un mergeCondition . Această condiție specifică modul în care rândurile din tabelul sursă sunt potrivite cu tabelul țintă. Dacă sunteți familiarizat cu INNER JOINS, vă puteți gândi la aceasta ca la condiția de asociere utilizată pentru a potrivi rândurile.

De obicei, veți potrivi un identificator unic, cum ar fi o cheie primară. Dacă tabelul sursă era NewProduct și ProductMaster țintă și cheia principală pentru ambele ProductID, atunci o condiție bună de îmbinare de utilizat ar fi:

NewProduct.ProductID = ProductMaster.ProductID

Rezultă o condiție de îmbinare într-una din cele trei stări: MATCHED, NOT MATCHED, or NE MATCHED BY SOURCE.

Condiții de îmbinare

Să trecem la ce înseamnă diferitele condiții:

MATCHED – acestea sunt rânduri care îndeplinesc condiția de potrivire. Sunt comune atât tabelelor sursă, cât și tabelelor țintă. În diagrama noastră, acestea sunt afișate ca verzi. Când utilizați această condiție într-o declarație de fuziune, vă; cel mai mult ca actualizarea coloanelor rândului țintă cu valorile coloane sourceTable.

NOT MATCHED – Acest lucru este, de asemenea, cunoscut sub numele de NOT MATCHED BY TARGET; acestea sunt rânduri din tabelul sursă care nu s-au potrivit cu niciun rând din tabelul țintă. Aceste rânduri sunt reprezentate de zona albastră de deasupra. În majoritatea cazurilor, acestea pot fi folosite pentru a deduce că rândurile sursă ar trebui adăugate la targetTable.

NECORDATE DE SURSE – acestea sunt rânduri din tabelul țintă care nu au fost niciodată potrivite de o înregistrare sursă; acestea sunt rândurile din zona portocalie. Dacă scopul dvs. este să sincronizați complet datele targetTable cu sursa, atunci veți utiliza această condiție de potrivire pentru ȘTERGEREA rândurilor.

Dacă aveți probleme înțelegând cum funcționează, luați în considerare condiția de îmbinare. o condiție de asociere. RÂNDURILE din secțiunea verde reprezintă rânduri care corespund condiției de îmbinare, rândurile din secțiunea albastră sunt acele rânduri găsite în SourceTable, dar nu în țintă. Rândurile din secțiunea portocalie sunt acele rânduri găsite numai în țintă.

Oferiți aceste scenarii potrivite, puteți încorpora cu ușurință activități de adăugare, eliminare și actualizare într-o singură declarație pentru a sincroniza modificările între două tables.

Să vedem un exemplu.

Exemplu MERGE

Să presupunem că scopul nostru este să sincronizăm orice modificări aduse esqlProductSource cu esqlProductTarget. Iată o diagramă a acestor două tabele:

Notă: De dragul acestui exemplu am rulat scripturile despre care am vorbit în introducerea pentru a crea și a completa două tabele: esqlProductSource și esqlProductTarget.

Înainte de a construi instrucțiunea MERGE, să analizăm modul în care am sincroniza tabelul folosind UPDATE, INSERT și DELETE pentru a modifica, adăuga și eliminați rândurile din tabelul țintă.

Cred că odată ce vedeți cum facem acest lucru în mod individual, este mai logic să vedeți combinate într-o singură operație.

Utilizarea UPDATE pentru a sincroniza modificările de la un tabel la următor

Pentru a actualiza tabelul țintă cu valorile modificate în sursa produsului, putem folosi o instrucțiune UPDATE. Dat fiind faptul că ID-ul produsului este cheia primară a ambelor tabele, devine cea mai bună alegere a rândurilor noastre de potrivire între tabele.

Dacă am actualiza valorile coloanei din tabelul țintă folosind coloana sursă, am putea face acest lucru folosind următoarea declarație de actualizare

UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID

Această declarație va actualiza coloana din esqlProductTarget cu valorile corespunzătoare ale coloanei găsite în esqlProductSource pentru potrivirea ID-urilor produsului. pot identifica rândurile din tabelul sursă pe care trebuie să le inserăm în produsul vizat. Pentru a face acest lucru, putem folosi subinterogarea pentru a găsi rânduri în tabelul sursă care nu se află în țintă.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Notă: aș putea folosi și o îmbinare exterioară să facă la fel. Dacă sunteți interesat de ce, consultați acest articol.

Această declarație va insera un rând nou în esqlProductTarget din toate rândurile din esqlProductSource care nu se găsesc în esqlProductTarget.

Eliminarea Rows

Ultima activitate de sincronizare pe care trebuie să o facem, elimină toate rândurile din tabelul țintă care nu se află în SQL Source. Așa cum am făcut cu instrucțiunea insert, vom folosi o interogare. Dar de data aceasta vom identifica rânduri în esqlProductTarget care nu a fost găsit în esqlProductSource. Iată declarația DELETE pe care o putem folosi:

DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)

Acum că ați văzut cum să faceți diferitele operații individual, să vedem cum se reunesc în declarație merge.

Observați că acolo cea mai mare parte a ridicării grele se face prin condiția de îmbinare și rezultatele sale. În loc să trebuiască să configurați în mod repetat meciul, așa cum am făcut în instrucțiunea de ștergere, se face o dată.

Comparați din nou instrucțiunea Insert cu instrucțiunea de îmbinare de mai sus.

INSERT INTO esqlProductTarget (ProductID, Name, ProductNumber, Color)SELECT S.ProductID, S.Name, S.ProductNumber, S.ColorFROM esqlProductSource SWHERE NOT EXISTS (SELECT T.ProductID FROM esqlProductTarget T WHERE T.ProductID = S.ProductID)

Având în vedere că instrucțiunea MERGE stabilește tabelul sursă și țintă, precum și modul în care se potrivesc, toate culorile codate în roșu sunt redundante; prin urmare, nu în porțiunea de inserare a îmbinării.

Înregistrarea modificărilor MERGE folosind OUTPUT

Puteți utiliza clauza OUTPUT pentru a înregistra orice modificări. În acest caz, variabila specială $ action poate fi utilizată pentru înregistrarea acțiunii de îmbinare. Această variabilă va lua una din cele trei valori: „INSERT”, „UPDATE” sau „DELETE”.

Vom folosi în continuare exemplul nostru, dar de data aceasta vom înregistra modificările și vom rezuma modificări.

Dacă cele de mai sus se execută pe eșantion de date noi, se generează următorul rezumat: