Microsoft SQL Server
SAMENVOEGEN

Invoering

Syntaxis

• Volgens MSDN - https://msdn.microsoft.com/en-us/library/bb510625.aspx [MET <common_table_expression> [, ... n]] MERGE [TOP (expressie) [PERCENT]] [INTO] < target_table> [WITH (<merge_hint>)] [[AS] table_alias] GEBRUIK VAN <table_source> ON <merge_search_condition> [WHEN MATCHED [AND <clause_search_condition>] DAN <merge_matched>] [... n] [WHEN NOT MATCHED [BY DOEL] [EN <clause_search_condition>] DAN <merge_not_matched>] [WANNEER NIET DOOR BRON GEWIJZIGD [EN <clause_search_condition>] DAN <merge_matched>] [... n] [<output_clause>] [OPTION (<query_hint> [,. ..n])]; <target_table> :: = {[database_name. schemanaam. | schemanaam. ] target_table} <merge_hint> :: = {{[<table_hint_limited> [, ... n]] [[,] INDEX (index_val [, ... n])]}}} <table_source> :: = {table_or_view_name [ [AS] table_alias] [<tablesample_clause>] [WITH (table_hint [[,] ... n])] | rijenet_functie [[AS] table_alias] [(bulk_column_alias [, ... n])] | user_defined_function [[AS] table_alias] | OPENXML <openxml_clause> | deriv_table [AS] table_alias [(column_alias [, ... n])] | <joined_table> | <draaischijf> | <unpivoted_table>} <merge_search_condition> :: = <search_condition> <merge_matched> :: = {UPDATE SET <set_clause> | DELETE} <set_clause> :: = SET {column_name = {expression | STANDAARD | NULL} | {udt_column_name. {{property_name = expression | field_name = expression} | method_name (argument [, ... n])}} | kolomnaam {.WRITE (expressie, @Offset, @Length)} | @variable = expressie | @variable = column = expression | kolomnaam {+ = | - = | * = | / = | % = | & = | ^ = | | =} expressie | @variable {+ = | - = | * = | / = | % = | & = | ^ = | | =} expressie | @variable = kolom {+ = | - = | * = | / = | % = | & = | ^ = | | =} expressie} [, ... n] <merge_not_matched> :: = {INSERT [(column_list)] {VALUES (values_list) | DEFAULT VALUES}} <clause_search_condition> :: = <search_condition> :: = {[NOT] | (<search_condition>)} [{AND | OF} [NIET] {| (<search_condition>)}] [, ... n] :: = {expression {= | <> | ! = |

  | > = | ! > | <| <= | ! <} expressie | string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character'] | expressie [NIET] TUSSEN expressie EN expressie | expressie IS [NIET] NULL | BEVAT ({column | *}, '<bevat_search_condition>') | FREETEXT ({column | *}, 'freetext_string') | expressie [NOT] IN (subquery | expressie [, ... n]) | expressie {= | <> | ! = | | > = | ! > | <| <= | ! <} {ALL | ENKELE | ANY} (subquery) | BESTAAT (subquery)} <output_clause> :: = {[OUTPUT <dml_select_list> INTO {@table_variable | output_table} [(column_list)]] [OUTPUT <dml_select_list>]} <dml_select_list> :: = {<column_name> | scalar_expression} [[AS] column_alias_identifier] [, ... n] <column_name> :: = {DELETED | INGEBRACHT | from_table_name}. {* | kolomnaam} | $ actie

Opmerkingen

MERGE om in te voegen / bijwerken / verwijderen

MERGE INTO targetTable

USING sourceTable 
ON (targetTable.PKID = sourceTable.PKID)

WHEN MATCHED AND (targetTable.PKID > 100) THEN
    DELETE

WHEN MATCHED AND (targetTable.PKID <= 100) THEN 
    UPDATE SET 
        targetTable.ColumnA = sourceTable.ColumnA, 
        targetTable.ColumnB = sourceTable.ColumnB

WHEN NOT MATCHED THEN
    INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);

WHEN NOT MATCHED BY SOURCE THEN
    DELETE
; --< Required

Beschrijving:

• MERGE INTO targetTable - te wijzigen tabel
• USING sourceTable - gegevensbron (kan een tabel of weergave of een tabelwaardige functie zijn)
• ON ... - join-voorwaarde tussen targetTable en sourceTable .
• WHEN MATCHED - acties die moeten worden ondernomen wanneer een match wordt gevonden
• • AND (targetTable.PKID > 100) - aanvullende voorwaarde (n) waaraan moet worden voldaan om de actie te kunnen ondernemen
• THEN DELETE - verwijder het overeenkomende record uit de targetTable
• THEN UPDATE - update kolommen van overeenkomende record gespecificeerd door SET ....
• WHEN NOT MATCHED - acties die moeten worden ondernomen wanneer de match niet wordt gevonden in targetTable
• WHEN NOT MATCHED BY SOURCE - acties die moeten worden ondernomen wanneer geen match wordt gevonden in sourceTable

reacties:

Als een specifieke actie niet nodig is, laat dan de voorwaarde weg, bijv. Verwijderen WHEN NOT MATCHED THEN INSERT voorkomt dat records worden ingevoegd

De instructie Merge vereist een afsluitende puntkomma.

beperkingen:

• WHEN MATCHED INSERT actie niet toestaat
• UPDATE actie kan een rij slechts eenmaal bijwerken. Dit houdt in dat de join-voorwaarde unieke overeenkomsten moet opleveren.

Samenvoegen met CTE-bron

WITH SourceTableCTE AS
(
    SELECT * FROM SourceTable
)
MERGE  
 TargetTable AS target
USING SourceTableCTE AS source  
ON (target.PKID = source.PKID)
WHEN MATCHED THEN     
    UPDATE SET target.ColumnA = source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (ColumnA) VALUES (Source.ColumnA);

MERGE met behulp van afgeleide brontabel

MERGE INTO TargetTable  AS Target  
USING (VALUES (1,'Value1'), (2, 'Value2'), (3,'Value3'))  
       AS Source (PKID, ColumnA)  
ON Target.PKID = Source.PKID 
WHEN MATCHED THEN 
    UPDATE SET target.ColumnA= source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (PKID, ColumnA) VALUES (Source.PKID, Source.ColumnA);

Voorbeeld samenvoegen - Bron- en doeltabel synchroniseren

Om de MERGE-verklaring te illustreren, overweeg de volgende twee tabellen -

1. dbo.Product : deze tabel bevat informatie over het product dat het bedrijf momenteel verkoopt

2. dbo.ProductNew : deze tabel bevat informatie over het product dat het bedrijf in de toekomst zal verkopen.

De volgende T-SQL maakt en vult deze twee tabellen

IF OBJECT_id(N'dbo.Product',N'U') IS NOT NULL 
DROP TABLE dbo.Product
GO

CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

IF OBJECT_id(N'dbo.ProductNew',N'U') IS NOT NULL 
DROP TABLE dbo.ProductNew
GO

CREATE TABLE dbo.ProductNew (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

INSERT INTO dbo.Product VALUES(1,'IPod',300)
,(2,'IPhone',400)
,(3,'ChromeCast',100)
,(4,'raspberry pi',50)

INSERT INTO dbo.ProductNew VALUES(1,'Asus Notebook',300)
,(2,'Hp Notebook',400)
,(3,'Dell Notebook',100)
,(4,'raspberry pi',50)

Stel nu dat we de dbo.Product-doeltabel willen synchroniseren met de dbo.ProductNieuwe tabel. Hier is het criterium voor deze taak:

1. Product dat bestaat in zowel de dbo.ProductNieuwe brontabel en de dbo.Product doeltabel worden bijgewerkt in de dbo.Product doeltabel met nieuwe nieuwe producten.

2. Elk product in de dbo.Product Nieuwe brontabel die niet bestaat in de dob.Product-doeltabel wordt ingevoegd in de dbo.Product-doeltabel.

3. Elk product in de dbo.Product-doeltabel dat niet bestaat in de dbo.ProductNieuwe brontabel moet worden verwijderd uit de dbo.Product-doeltabel. Hier is de MERGE-instructie om deze taak uit te voeren.

MERGE dbo.Product AS SourceTbl 
USING dbo.ProductNew AS TargetTbl ON (SourceTbl.ProductID = TargetTbl.ProductID)
WHEN MATCHED 
            AND SourceTbl.ProductName <> TargetTbl.ProductName
            OR SourceTbl.Price <> TargetTbl.Price
    THEN UPDATE SET SourceTbl.ProductName = TargetTbl.ProductName,
                SourceTbl.Price = TargetTbl.Price
WHEN NOT MATCHED 
    THEN INSERT (ProductID, ProductName, Price)
         VALUES (TargetTbl.ProductID, TargetTbl.ProductName, TargetTbl.Price)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

Opmerking: puntkomma moet aanwezig zijn aan het einde van MERGE-instructie.

Samenvoegen met EXCEPT

Gebruik EXCEPT om updates van ongewijzigde records te voorkomen

MERGE TargetTable targ
USING SourceTable AS src
    ON src.id = targ.id
WHEN MATCHED
    AND EXISTS (
        SELECT src.field
        EXCEPT
        SELECT targ.field
        )
    THEN
        UPDATE
        SET field = src.field
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            id
            ,field
            )
        VALUES (
            src.id
            ,src.field
            )
WHEN NOT MATCHED BY SOURCE
    THEN
        DELETE;