Does MERGE use the same semantics as INSERT does for applying default values for inserted columns that are (a) not provided or (b) provided with NULL values? If not, can someone point to a description of those semantics? (I haven't found them in the online help.)
I realize this sounds like an odd question, but I'm getting odd results, and it would help to know whether to look for a straightforward mistake or if there are more subtle rules that I'm just not taking into account.
(Note: I'm only asking if the MERGE rules are different than for INSERT. I won't subject you all to do my debugging for me.)
asked 03 Nov '10, 01:04
The MERGE statement uses the same semantics as an INSERT statement when the MERGE's NOT MATCHED INSERT clause is invoked.
Here is an example that illustrates the MERGE statement INSERT clause.
The output is:
Note that when null is given as the value, NULL is inserted (e.g. column u), and when the column is omitted from the insert clause (e.g. column t) or when 'default' is given as the value of the column (e.g. column v) then the default value (e.g. column v's default is 'defaults') is inserted into the row. This is the same behaviour as is seen when the value was inserted by an INSERT statement (e.g. row where i = 10)
answered 06 Nov '10, 01:21