CODEWITHSUNDEEP
sql-servert-sqlsqlxmlsql-merge
HABJAN
HABJAN

Reputation: 9328

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

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: enter image description here

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

Upvotes: 2

Views: 81

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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...

Upvotes: 2

Related Questions