T-Sql MERGE statement update when criteria matches newly inserted record

Question

This is the XML I have:

<?xml version="1.0" encoding="UTF-8"?>
<Data>
   <Record>
      <ServerId>1</ServerId>
      <CompanyId>1</CompanyId>
      <InstanceId>2</InstanceId>
      <TemplateId>23</TemplateId>
      <ContactId>11052</ContactId>
      <RecordId>11462</RecordId>
      <TaskId>677</TaskId>
      <EntryDate>2016-04-21 14:17:02:813</EntryDate>
      <EntryKey>key_test_1</EntryKey>
      <EntryValue>value_test_1</EntryValue>
   </Record>
   <Record>
      <ServerId>1</ServerId>
      <CompanyId>1</CompanyId>
      <InstanceId>2</InstanceId>
      <TemplateId>23</TemplateId>
      <ContactId>11052</ContactId>
      <RecordId>11462</RecordId>
      <TaskId>677</TaskId>
      <EntryDate>2016-04-21 14:17:02:873</EntryDate>
      <EntryKey>key_test_2</EntryKey>
      <EntryValue>value_test_2</EntryValue>
   </Record>
   <Record>
      <ServerId>1</ServerId>
      <CompanyId>1</CompanyId>
      <InstanceId>2</InstanceId>
      <TemplateId>23</TemplateId>
      <ContactId>11052</ContactId>
      <RecordId>11462</RecordId>
      <TaskId>677</TaskId>
      <EntryDate>2016-04-21 14:17:02:935</EntryDate>
      <EntryKey>key_test_1</EntryKey>
      <EntryValue>value_test_3</EntryValue>
   </Record>
</Data>

and this is MERGE statement that inserts or updates this data into single table:

merge WRF_REPOSITORY_CUSTOM_DATA rep
using (select 
        entity.value('ServerId[1]', 'int') as ServerId,
        entity.value('CompanyId[1]', 'int') as CompanyId,
        entity.value('InstanceId[1]', 'int') as InstanceId,
        entity.value('TemplateId[1]', 'int') as TemplateId,
        entity.value('ContactId[1]', 'bigint') as ContactId,
        entity.value('RecordId[1]', 'bigint') as RecordId,
        entity.value('TaskId[1]', 'bigint') as TaskId,
        entity.value('EntryDate[1]', 'datetime') as EntryDate,
        entity.value('EntryKey[1]', 'varchar(max)') as EntryKey,
        entity.value('EntryValue[1]', 'varchar(max)') as EntryValue
        from @xmlInsertOrReplace.nodes('/Data/Record') as T(entity)) as dat
on 
    rep.ServerId = dat.ServerId and 
    rep.CompanyId = dat.CompanyId and
    rep.InstanceId = dat.InstanceId and
    rep.TemplateId = dat.TemplateId and
    rep.ContactId = dat.ContactId and 
    rep.RecordId = dat.RecordId and
    rep.EntryKey = dat.EntryKey
when MATCHED then update set
    rep.TaskId = dat.TaskId,
    rep.EntryDate = dat.EntryDate,
    rep.EntryValue = dat.EntryValue
when NOT MATCHED then
    insert (ServerId, CompanyId, InstanceId, TemplateId, ContactId, RecordId, TaskId, EntryDate, EntryKey, EntryValue)
    values (dat.ServerId, dat.CompanyId, dat.InstanceId, dat.TemplateId, dat.ContactId, dat.RecordId, dat.TaskId, dat.EntryDate, dat.EntryKey, dat.EntryValue);

When there are no records in table, what I was expecting is that the first xml record will be inserted, second xml record will be inserted, third xml record will update first table record as it matches the criteria. What actually happens is I get 3 inserts instead 2 inserts and 1 update.

Result screenshot:

Is there a way to force MERGE statement to do COMMIT or something after each insert or update? I don't want to group xml records or select the max/last one.

UPDATE:

I use one more merge action similar to the first one. The only difference is that this one concats the values.

when MATCHED then update set
    rep.TaskId = dat.TaskId,
    rep.EntryDate = dat.EntryDate,
    rep.EntryValue = case len(isnull(rep.EntryValue, '')) when 0 then dat.EntryValue else rep.EntryValue + ';' + dat.EntryValue end

And the expected result should be:

key_test_1 | value_test_1;value_test_3
key_test_2 | value_test_2

No. MERGE is a single statement. If you want to first do inserts and then updates you need to use two separate statements. — Sean Lange, Apr 21 '16 at 13:59
@Sean Lange is correct. The MERGE statement compares the data in the existing table with the data in the parsed XML data. — Steven, Apr 21 '16 at 14:02
*"I don't want to group xml records or select the max/last one."* - Why not? This seems like the solution, why bother inserting the first record at all if all the fields will be over written with the 3rd record anyway. — GarethD, Apr 21 '16 at 14:06
@GarethD: because I have one more merge statement that does a same but it concats the value with the existing one. So, a same xml data, insert, inser, update (concat 3rd xml record value to newly inserted 1st table record). And this can be done from multiple applications within a same machine at a same time... — HABJAN, Apr 21 '16 at 14:09
HABJAN, I just placed a comment below my answer to reflect this... — Shnugo, Apr 21 '16 at 14:14
BTW, are you aware that ["The MERGE statement cannot update the same row more than once"](https://msdn.microsoft.com/en-AU/library/bb510625.aspx) (attempt to do it will return an error)? You need to group/concatenate your source rows before doing the MERGE. — Vladimir Baranov, Apr 21 '16 at 14:20
@VladimirBaranov: Thansk for letting me know, I was not aware of that. This made me change my logic. — HABJAN, Apr 22 '16 at 13:23

score 2 · Accepted Answer · answered Apr 21 '16 at 14:06

You might try this:

Use ROW_NUMBER to find the most current record in your XML and just ignore the older one, which would be overwritten anyway...

WITH shreddedXML AS
(
    select 
        entity.value('ServerId[1]', 'int') as ServerId,
        entity.value('CompanyId[1]', 'int') as CompanyId,
        entity.value('InstanceId[1]', 'int') as InstanceId,
        entity.value('TemplateId[1]', 'int') as TemplateId,
        entity.value('ContactId[1]', 'bigint') as ContactId,
        entity.value('RecordId[1]', 'bigint') as RecordId,
        entity.value('TaskId[1]', 'bigint') as TaskId,
        entity.value('EntryDate[1]', 'datetime') as EntryDate,
        entity.value('EntryKey[1]', 'varchar(max)') as EntryKey,
        entity.value('EntryValue[1]', 'varchar(max)') as EntryValue
    from @xmlInsertOrReplace.nodes('/Data/Record') as T(entity)
)
,SearchForMostActualRows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ServerId,CompanyId,InstanceId,TemplateId,ContactId,RecordId,EntryKey ORDER BY EntryDate DESC) AS Nr
          ,*
    FROM shreddedXML
)
SELECT * 
FROM SearchForMostActualRows
WHERE Nr=1

Now do your MERGE with this...

The problem is that I can't ignore any record. I'll update my question with the reason. — HABJAN, Apr 21 '16 at 14:11
@HABJAN I just read the reason. Just change my query to `SELECT * INTO #tempTable`. In the next step you do the `MERGE` with `SELECT * FROM #tempTable WHERE Nr=1` and after this you do what ever you want with `SELECT * FROM #tempTable WHERE Nr>1` — Shnugo, Apr 21 '16 at 14:14
Even this does not solve my problem, it pointed me into right direction. Thanks. — HABJAN, Apr 22 '16 at 13:23

T-Sql MERGE statement update when criteria matches newly inserted record

1 Answers1