Section courante

A propos

Section administrative du site

MERGE

Fusion
  Microsoft SQL Server

Syntaxe

[ 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 ] ) ]
;

target_table ::=
{
[ database_name . schema_name . | schema_name . ]
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 ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML openxml_clause
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| joined_table
| pivoted_table
| unpivoted_table
}

merge_search_condition ::=
search_condition

merge_matched::=
{ UPDATE SET set_clause | DELETE }

set_clause::=
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] ) }
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression
| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
} [ ,...n ]

merge_not_matched::=
{
INSERT [ ( column_list ) ]
{ VALUES ( values_list )
| DEFAULT VALUES }
}

clause_search_condition ::=
search_condition

search condition ::=
MATCH(graph_search_pattern) | search_condition_without_match | search_condition AND search_condition

search_condition_without_match ::=
{ [ NOT ] predicate | ( search_condition_without_match )
[ { AND | OR } [ NOT ] { predicate | ( search_condition_without_match ) } ]
[ ,...n ]

predicate ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression
[ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS
( { column | * } , 'contains_search_condition' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }

graph_search_pattern ::=
{ node_alias {
{ <-( edge_alias )- }
| { -( edge_alias )-> }
node_alias
}
}

node_alias ::=
node_table_name | node_table_alias

edge_alias ::=
edge_table_name | edge_table_alias

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 | INSERTED | from_table_name } . { * | column_name }
| $action

Description

Cette instruction permet d'effectuer des opérations d'insertions, de mise à jour ou de suppression dans une table destinataire basé sur le résultat d'un jointure d'une table source.



Dernière mise à jour : Vendredi, le 19 Juin 2020