Introduksjon til MERGE Statement og SQL Server Data Modification
MERGE-setningen brukes til å gjøre endringer i en tabell basert på verdier som er matchet fra anther. Den kan brukes til å kombinere innsetting, oppdatering og sletting av operasjoner i en setning. I denne artikkelen vil vi undersøke hvordan du bruker MERGE-setningen. Vi diskuterer noen beste fremgangsmåter, begrensninger og oppsummering med flere eksempler.
Dette er den femte artikkelen i en serie artikler. Du kan starte på begynnelsen ved å lese Introduksjon til SQL Server Data Modification Statements.
Alle eksemplene for denne leksjonen er basert på Microsoft SQL Server Management Studio og AdventureWorks2012-databasen. Du kan komme i gang med disse gratisverktøyene ved å bruke guiden min Komme i gang med SQL Server
Før vi begynner
Selv om denne artikkelen bruker AdventureWorks-databasen som eksempler, har jeg bestemt meg for å lage flere eksempletabeller for bruk i databasen for å bedre illustrere konseptene som dekkes. Du finner skriptet du trenger for å kjøre her. Legg merke til at det er en spesiell seksjon som gjelder MERGE.
Grunnleggende struktur
MERGE-setningen kombinerer INSERT, DELETE og UPDATE-operasjoner i en tabell. Når du har forstått hvordan det fungerer, vil du se det forenkler prosedyren ved å bruke alle tre setningene separat for å synkronisere data.
Nedenfor er et generalisert format for flettingsuttalelsen.
MERGE targetTableUsing sourceTableON mergeConditionWHEN MATCHEDTHEN updateStatementWHEN NOT MATCHED BY TARGETTHEN insertStatementWHEN NOT MATCHED BY SOURCETHEN deleteStatement
Sammenslåingsuttalelsen fungerer ved hjelp av to tabeller, kildetabellen og måltabellen. Måltabellen er tabellen som skal modifiseres basert på data som finnes i kildetabellen.
De to tabellene sammenlignes ved hjelp av en mergeCondition . Denne tilstanden spesifiserer hvordan rader fra kildetabellen blir matchet med måltabellen. Hvis du er kjent med INNER JOINS, kan du tenke på dette som sammenkoblingsbetingelsen som brukes til å matche rader.
Vanligvis vil du matche en unik identifikator, for eksempel en primærnøkkel. Hvis kildetabellen var NewProduct og mål ProductMaster og den primære nøkkelen for begge ProductID, vil en god fletttilstand å bruke være:
NewProduct.ProductID = ProductMaster.ProductID
En resultat av en sammenslåingstilstand i en av tre tilstander: MATCHED, NOT MATCHED, or NOT MATCHED BY SOURCE.
Slå sammen forhold
La oss gå gjennom hva de forskjellige forholdene betyr:
MATCHED – dette er rader som tilfredsstiller kamptilstanden. De er felles for både kilde- og måltabellene. I diagrammet vårt er de vist som grønne. Når du bruker denne tilstanden i en fusjonsuttalelse, mest som å oppdatere målradkolonnene med kildetabellkolonneverdier.
IKKE MATCHED – Dette er også kjent som NOT MATCHED BY TARGET; Dette er rader fra kildetabellen som ikke samsvarte med noen rader i måltabellen. Disse radene er representert med det blå området ovenfor. I de fleste tilfeller kan det brukes til å slutte at kildene Rader skal legges til måltabellen.
IKKE MATCHED BY SOURCE – dette er rader i måltabellen som aldri ble matchet med en kildepost; dette er radene i det oransje området. Hvis målet ditt er å synkronisere måltabeldataene fullstendig med kilden, vil du bruke denne samsvarsvilkåret til å SLETTE rader.
Hvis du har problemer med å forstå hvordan dette fungerer, bør du vurdere å slå sammen tilstanden som en tilknytningstilstand. RADER i den grønne delen representerer rader som samsvarer med flettebetingelsen. Rader i den blå delen er de radene som finnes i kildetabellen, men ikke i målet. Radene i den oransje delen er de radene som bare finnes i målet.
Gi disse samsvarende scenariene, du kan enkelt innlemme legge til, fjerne og oppdatere aktiviteter i en enkelt uttalelse for å synkronisere endringene mellom to tabeller.
La oss se på et eksempel.
MERGE Eksempel
La oss anta at vårt mål er å synkronisere alle endringer som er gjort i esqlProductSource med esqlProductTarget. Her er et diagram over disse to tabellene:
Merk: Av hensyn til dette eksemplet kjørte jeg manusene jeg snakket om i innledningen for å opprette og fylle ut to tabeller: esqlProductSource og esqlProductTarget.
Før vi konstruerer MERGE-setningen, la oss se på hvordan vi ville synkronisere tabellen ved hjelp av UPDATE, INSERT og DELETE-setningen for å endre, legge til , og fjern rader i måltabellen.
Jeg tror at når du først ser hvordan vi gjør dette hver for seg, er det mer fornuftig å se kombinert til en enkelt operasjon.
Å bruke UPDATE til å synkronisere endringer fra ett bord til det neste
For å oppdatere måltabellen med de endrede verdiene i produktkilden, kan vi bruke en UPDATE-setning. Gitt at ProductID er begge tabellens primære nøkkel, blir det vårt beste valg for å matche radene mellom bordene.
Hvis vi skulle oppdatere kolonneverdiene i måltabellen ved hjelp av kildekolonnene, kan vi gjøre det ved hjelp av følgende oppdateringsuttalelse
UPDATE esqlProductTargetSET Name = S.Name, ProductNumber = S.ProductNumber, Color = S.ColorFROM esqlProductTarget T INNER JOIN esqlProductSource S ON S.ProductID = T.ProductID
Denne uttalelsen vil oppdatere kolonnen i esqlProductTarget med tilsvarende kolonneverdier som finnes i esqlProductSource for samsvarende produktID.
INSERT-rader funnet i en tabell, men ikke den andre
La oss nå se hvordan vi kan identifisere radene fra kildetabellen som vi trenger å sette inn i produktmålet. For å gjøre dette kan vi bruke underspørring for å finne rader i kildetabellen som 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)
Merk: Jeg kan også bruke en ytre sammenføyning å gjøre det samme. Hvis du er interessert i hvorfor, kan du sjekke ut denne artikkelen.
Denne uttalelsen vil sette inn en ny rad i esqlProductTarget fra alle rader i esqlProductSource som ikke finnes i esqlProductTarget.
Fjerner Rader
Den siste synkroniseringsaktiviteten vi trenger å gjøre, den fjerner alle rader i måltabellen som ikke er i SQL Source. Som vi gjorde med innsettingsuttalelsen, bruker vi et underspørsmål. Men denne gangen identifiserer vi rader i esqlProductTarget ikke funnet i esqlProductSource. Her er DELETE-setningen vi kan bruke:
DELETE esqlProductTargetFROM esqlProductTarget TWHERE NOT EXISTS (SELECT S.ProductID FROM esqlProductSource S WHERE T.ProductID = S.ProductID)
Nå som du har sett hvordan du gjør de forskjellige operasjonene hver for seg, kan vi se hvordan de kommer sammen i merge statement.
Legg merke til at det meste av tunge løft gjøres av sammenslåingstilstanden og dens resultater. I stedet for å måtte sette opp kampen gjentatte ganger, slik vi gjorde med slettingsuttrykket, gjøres det en gang.
Sammenlign igjen Sett inn-setningen med fletningsuttalelsen 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)
Gitt MERGE-setningen etableres kilde- og måltabellen, samt hvordan de samsvarer, er alt fargekodet i rødt overflødig; derfor ikke i innsettingsdelen av flettingen.
Logging MERGE Endringer ved hjelp av OUTPUT
Du kan bruke OUTPUT-setningen til å logge eventuelle endringer. I dette tilfellet kan den spesielle variabelen $ handling brukes til å logge flettehandlingen. Denne variabelen tar en av tre verdier: «INSERT», «UPDATE» eller «DELETE».
Vi fortsetter å bruke eksemplet vårt, men denne gangen logger vi endringene og oppsummerer endres.
Hvis det ovennevnte kjøres på ferske eksempeldata, genereres følgende sammendrag:
