The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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

Dan%20Konigsbach's gravatar image

Dan Konigsbach
accept rate: 0%

Without me knowing the particular rules: As the INSERT/UPDATE/DELETE actions of a MERGE statement are recorded in the transaction log as individual INSERT/UPDATE/DELETE operations, that might give you a clue of what exact operation is executed under the covers.

(04 Nov '10, 11:15) Volker Barth

FWIW: I should have added that DBTRAN will be needed to check the log's contents:)

(05 Nov '10, 08:13) Volker Barth

@Dan: Can you elaborate on the "odd results" that you are seeing? AFAIK (and I have not checked the code) when the MERGE statement invokes the INSERT clause for a NOT MATCHED tuple it is equivalent to executing an INSERT statement and therefore should end up inserting the same thing.

(06 Nov '10, 00:54) Mark Culp

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.

create table foo(
         i int          default autoincrement,
         s varchar(100) default 'specified',
         t varchar(100) default 'not specified',
         u varchar(100) default 'nulls',
         v varchar(100) default 'defaults'

merge into foo
  using ( select row_num from sa_rowgenerator( 1, 4 ) ) as s
  on foo.i = s.row_num
when not matched then
  insert( i, s, u, v ) values( s.row_num, s.row_num*s.row_num, null, default );

insert into foo( i, s, u, v ) values( 10, 10*10, null, default );

select * from foo;

The output is:

i  s   t             u      v
1  1   not specified (NULL) defaults
2  4   not specified (NULL) defaults
3  9   not specified (NULL) defaults
4  16  not specified (NULL) defaults
10 100 not specified (NULL) defaults

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)

permanent link

answered 06 Nov '10, 01:21

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 03 Nov '10, 01:04

question was seen: 837 times

last updated: 06 Nov '10, 01:21