use two merges Matched then on sql server server

Asked

Viewed 27 times

-1

I’d like to know how to use the second merge

I need the following case, knowing that the source is a log, will not happen the "WHEN NOT MATCHED BY SOURCE THEN"

MERGE tabelaTarget AS target  
USING 
   (SELECT id, codigo, flag_deletado from tabelaSource) AS source 
ON (target.ID = source.ID and target.flag_deletado = '')  
WHEN MATCHED AND (target.codigo <> source.codigo and source.flag_deletado = '') THEN
   UPDATE SET target.codigo= source.codigo
WHEN MATCHED THEN
   UPDATE SET target.flag_deletado = '*'

WHEN NOT MATCHED BY TARGET THEN  

        INSERT (codigo, flag_deletado)  
        VALUES (source.codigo, flag_deletado='')  
 OUTPUT deleted.*, $action, inserted.* INTO tabelaLog;

In the documentation it says the following:

"The MERGE instruction can have at most two WHEN MATCHED clauses. If two clauses are specified, the first should be accompanied by an AND clause. For any specified line, the second WHEN MATCHED clause will apply only if the first is not. If there are two WHEN MATCHED clauses, one of them must specify an UPDATE action and the other, a DELETE action. When UPDATE is specified in the clause and more than one line matches a target_table line based on, SQL Server will return an error. The MERGE statement cannot update the same line more than once, nor update and delete the same line."

I understand that if the first has the second case AND should happen normally as I wrote in the script. But in Doc too, there’s the syntax I couldn’t understand.

I understand that if the first has the second case AND should happen normally as I wrote in the script. But in Doc too, there’s the syntax I couldn’t understand.

[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source>
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ <output_clause> ]  
    [ OPTION ( <query_hint> [ ,...n ] ) ];

<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }

How do I put these blocks together? I see that it has ; As I call merge_matched, I don’t really understand, I can’t use the Deleted, because the database I’m bringing in, it’s not normalized, it can give several problems in my database to the right that is normalized.

sql-server merge

  • But what is the purpose of the consultation? Explain the context to facilitate the suggestions.

  • José Diz, the goal is for the target table to be updated by an origin log table, because the actual source table, if I were to merge with it, would take hours, are millions (almost billion) of records. I’m thinking of putting the data in a temporary table, first I merge, with the return also of the deleted flag, update the target table.

1 answer

0


I decided that way

MERGE tabelaTarget AS target  
USING 
   (SELECT id, codigo, flag_deletado from tabelaSource) AS source 
ON (target.ID = source.ID and target.flag_deletado = '')  
WHEN MATCHED AND (target.codigo <> source.codigo 
   or source.flag_deletado = '*') THEN --colocado o or e flag_deletado = '*'

  UPDATE SET target.codigo = iif(source.flag_deletado = '*', target.codigo ,source.codigo,
   target.flag_deletado = iif(source.flag_deletado = '*', target.flag_deletado, )
--foi utilizado o iif para resolver a duplicidade do matched
WHEN NOT MATCHED BY TARGET THEN     
   INSERT (codigo, flag_deletado)  
   VALUES (source.codigo, flag_deletado='')  
 OUTPUT deleted.*, $action, inserted.* INTO tabelaLog

;

Browser other questions tagged sql-server merge

You are not signed in. Login or sign up in order to post.