Introduktion til fletningserklæringen og modificering af SQL Server-data

MERGE-sætningen bruges til at foretage ændringer i en tabel baseret på værdier, der er matchet fra anther. Det kan bruges til at kombinere indsættelse, opdatering og sletning af operationer i en sætning. I denne artikel undersøger vi, hvordan du bruger MERGE-erklæringen. Vi diskuterer nogle af de bedste fremgangsmåder, begrænsninger og afslutning med flere eksempler.

Dette er den femte artikel i en serie af artikler. Du kan starte i starten ved at læse Introduktion til SQL Server Data Modification Statements.

Alle eksemplerne til denne lektion er baseret på Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang ved hjælp af disse gratis værktøjer ved hjælp af min guide Kom godt i gang ved hjælp af SQL Server

Før vi begynder

Selvom denne artikel bruger AdventureWorks-databasen til sine eksempler, har jeg besluttet at oprette flere eksempletabeller til brug i databasen for bedre at illustrere de dækkede begreber. Du kan finde det script, du skal køre her. Bemærk, at der er et specielt afsnit, der vedrører FUSION.

Grundlæggende struktur

MERGE-sætningen kombinerer INSERT-, DELETE- og UPDATE-operationer i en tabel. Når du først har forstået, hvordan det fungerer, vil du se, at det forenkler proceduren med at bruge alle tre udsagn separat til at synkronisere data.

Nedenfor er et generaliseret format til flettsætningen.

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

Fletningserklæringen fungerer ved hjælp af to tabeller, sourceTable og targetTable. Måltabellen er den tabel, der skal ændres baseret på data indeholdt i kildetabellen.

De to tabeller sammenlignes ved hjælp af en mergeCondition . Denne betingelse specificerer, hvordan rækker fra kildetabellen matches med måltabellen. Hvis du er fortrolig med INNER JOINS, kan du tænke på dette som den sammenføjningsbetingelse, der bruges til at matche rækker.

Du vil typisk matche en unik identifikator, f.eks. En primær nøgle. Hvis kildetabellen var NewProduct og target ProductMaster og den primære nøgle for begge ProductID, ville en god fletningsbetingelse være:

NewProduct.ProductID = ProductMaster.ProductID

En fletilstand resulterer i en af tre tilstande: MATCHED, NOT MATCHED eller NOT MATCHED BY SOURCE.

Flet betingelser

Lad os se på, hvad de forskellige betingelser betyder:

MATCHED – dette er rækker, der opfylder kamptilstanden. De er fælles for både kilde- og måltabellerne. I vores diagram vises de som grønne. Når du bruger denne betingelse i en fusionserklæring, vil du; mest som at opdatere målrækkolonnerne med sourceTable-kolonneværdier.

NOT MATCHED – Dette er også kendt som NOT MATCHED BY TARGET; dette er rækker fra kildetabellen, der ikke matchede nogen rækker i måltabellen. Disse rækker er repræsenteret af det blå område ovenfor. I de fleste tilfælde kan de bruges til at udlede, at kildens rækker skal føjes til måltabellen.

IKKE MATCHED VED KILDE – dette er rækker i måltabellen, der aldrig blev matchet af en kildepost; disse er rækkerne i det orange område. Hvis dit mål er at synkronisere måltabeldataene fuldstændigt med kilden, skal du bruge denne matchbetingelse til at SLETTE rækker.

Hvis du har problemer med at forstå, hvordan dette fungerer, skal du overveje, at fletningsbetingelsen er som en tilslutningstilstand. RÆKER i det grønne afsnit repræsenterer rækker, der matcher fletningsbetingelsen, rækker i det blå afsnit er de rækker, der findes i kildetabellen, men ikke i målet. Rækkerne i den orange sektion er de rækker, der kun findes i målet.

Giv disse matchende scenarier, du er i stand til nemt at inkorporere tilføj, fjern og opdater aktiviteter i en enkelt sætning for at synkronisere ændringer mellem to tabeller.

Lad os se på et eksempel.

FUSION Eksempel

Lad os antage, at vores mål er at synkronisere alle ændringer foretaget i esqlProductSource med esqlProductTarget. Her er et diagram over disse to tabeller:

Bemærk: Af hensyn til dette eksempel kørte jeg de scripts, jeg talte om i introduktionen til at oprette og udfylde to tabeller: esqlProductSource og esqlProductTarget.

Før vi konstruerer MERGE-sætningen, lad os se på, hvordan vi ville synkronisere tabellen ved hjælp af UPDATE, INSERT og DELETE-sætningen for at ændre, tilføje og fjern rækker i måltabellen.

Jeg tror, at når du først ser, hvordan vi gør dette individuelt, er det mere fornuftigt at se kombineret i en enkelt handling.

Brug UPDATE til at synkronisere ændringer fra en tabel til den næste

For at opdatere måltabellen med de ændrede værdier i produktkilden kan vi bruge en UPDATE-sætning. I betragtning af at ProductID er begge bordets primære nøgle, bliver det vores bedste valg matchrække mellem bordene.

Hvis vi skulle opdatere kolonneværdierne i måltabellen ved hjælp af kildekolonnens, kunne vi gøre det ved hjælp af følgende opdateringserklæring

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

Denne erklæring opdaterer kolonnen i esqlProductTarget med tilsvarende kolonneværdier, der findes i esqlProductSource til matchende produktID’er.

INSERT-rækker findes i en tabel, men ikke den anden

Lad os nu se, hvordan vi kan identificere rækkerne fra kildetabellen, som vi skal indsætte i produktmålet. For at gøre dette kan vi bruge underforespørgsel til at finde rækker i kildetabellen, der ikke er i målet.

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)

Bemærk: Jeg kunne også bruge en ydre sammenføjning at gøre det samme. Hvis du er interesseret i hvorfor, skal du tjekke denne artikel.

Denne erklæring indsætter en ny række i esqlProductTarget fra alle rækker i esqlProductSource, der ikke findes i esqlProductTarget.

Fjernelse Rækker

Den sidste synkroniseringsaktivitet, vi skal udføre, fjerner alle rækker i måltabellen, der ikke er i SQL Source. Som vi gjorde med indsætningserklæringen, bruger vi en underforespørgsel. Men denne gang identificerer vi rækker i esqlProductTarget ikke fundet i esqlProductSource. Her er DELETE-sætningen, vi kan bruge:

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

Nu hvor du har set, hvordan du udfører de forskellige operationer individuelt, kan vi se, hvordan de kommer sammen i fusionserklæring.

Bemærk, at der sker det meste af det tunge løft af fusionstilstanden og dens resultater. I stedet for at skulle opsætte kampen gentagne gange, som vi gjorde i sletningsudtalelsen, gøres det en gang.

Sammenlign igen Indsæt-sætningen med fusionsudsagnet ovenfor.

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)

Da MERGE-sætningen fastlægger kilde- og måltabellen, samt hvordan de matcher, er alt farvekodet i rødt overflødigt; derfor ikke i indsætningsdelen af fletningen.

Logning af MERGE ændringer ved hjælp af OUTPUT

Du kan bruge OUTPUT-klausulen til at logge eventuelle ændringer. I dette tilfælde kan den specielle variabel $ handling bruges til at logge sammenfletningen. Denne variabel tager en af tre værdier: “INSERT”, “UPDATE” eller “DELETE”.

Vi fortsætter med at bruge vores eksempel, men denne gang logger vi ændringerne og opsummerer ændringer.

Hvis ovenstående køres på nye eksempeldata, genereres følgende resume: