SQL Merge only inserting rows, not updating

Question

In SQL Server 2016, I'm trying to create/update records in my target table using Merge, but I think I'm running into a limitation where Merge only makes one pass through the data, so am getting all inserts and no updates.

In the target table (which is empty at the start of the Merge), there should be only one record per day/sitename/username, but it's creating multiple per day. What I'd like to do is this:

"Iterate through each row of the source table; if no row for that day/sitename/username exists in the target table, insert a new record; if a row for that day/sitename/username DOES exist in the target table, update it by adding to the BytesTransferred and incrementing the Connections (but if there are multiple of the same transactionID from the source table, only increment Connections once for that transactionID)."

Here is the created target table:

CREATE TABLE tbl_Report_Active_Users(
    ID [int] IDENTITY(1,1) NOT NULL,
    DayOfRecord [date] NULL,
    SiteName [nvarchar](50) NULL,
    UserName [nvarchar](50) NULL,
    Connections [bigint] NULL,
    BytesTransferred [numeric](18, 0) NULL,
CONSTRAINT [PK_tbl_Report_User_Activity] PRIMARY KEY CLUSTERED 
)
CREATE NONCLUSTERED INDEX IX_tbl_Report_Active_Users_dayofrecord_sitename_username ON
 tbl_Report_Active_Users (DayOfRecord,SiteName,UserName)

and here is the Merge code I'm trying:

MERGE INTO tbl_Report_Active_Users AS target
USING (SELECT p.ProtocolCommandID, p.TransactionID, p.BytesTransferred,
 CONVERT(date,p.Time_stamp) As DayOfRecord, p.SiteName, p.UserName
        FROM tbl_ProtocolCommands p) AS source
    (ProtocolCommandID, TransactionID, BytesTransferred, DayOfRecord, SiteName,
UserName)
ON (target.DayOfRecord = source.DayOfRecord AND target.SiteName = source.SiteName 
AND target.UserName = source.UserName)
WHEN MATCHED THEN
    UPDATE SET BytesTransferred = target.BytesTransferred + source.BytesTransferred,
        Connections = target.Connections + 
                CASE WHEN source.ProtocolCommandID = (SELECT MIN(p.ProtocolCommandID)
                FROM tbl_ProtocolCommands p 
                INNER JOIN tbl_Authentications a ON a.TransactionID = p.TransactionID
                WHERE p.TransactionID = source.TransactionID AND a.ResultID=0
                GROUP BY p.TransactionID
                ) THEN 1 ELSE 0 END
WHEN NOT MATCHED THEN
    INSERT (DayOfRecord,SiteName,UserName,Connections,BytesTransferred)
    VALUES (source.DayOfRecord,source.SiteName,source.UserName,1,
source.BytesTransferred);

I'm getting records like this in the target table:

ID  DayOfRecord SiteName    UserName    Connections BytesTransferred    (TransactionID)
1   2018-06-27  sales1  Jenkins 1   1829    333
2   2018-06-27  sales1  Jenkins 1   1829    333
3   2018-06-27  sales1  Jenkins 1   500     333
4   2018-06-27  sales1  Smith   1   500     376
5   2018-06-27  sales1  Smith   1   20559   391

But would expect this instead:

1   2018-06-27  sales1  Jenkins 1   4158
2   2018-06-27  sales1  Smith   2   21059

Any thoughts on how to best accomplish this?

score 0 · Answer 1 · answered Apr 25 '19 at 05:35

I think you are facing this issue because of join with "tbl_authentications" table. Rest of your merge statement is working fine,

    MERGE INTO tbl_report_active_users AS target 
using (SELECT p.protocolcommandid, 
              p.transactionid, 
              p.bytestransferred, 
              CONVERT(DATE, p.time_stamp) AS DayOfRecord, 
              p.sitename, 
              p.username 
       FROM   tbl_protocolcommands p) AS source
        (protocolcommandid, 
       transactionid, bytestransferred, dayofrecord, sitename, username) 

ON ( target.dayofrecord = source.dayofrecord 
     AND target.sitename = source.sitename 
     AND target.username = source.username ) 
WHEN matched THEN 
  UPDATE SET bytestransferred = target.bytestransferred 
                                + source.bytestransferred, 
             connections = target.connections + CASE WHEN 
                           source.protocolcommandid = (SELECT 
                           Min(p.protocolcommandid) FROM tbl_protocolcommands p 
                           --INNER JOIN 
                           --tbl_authentications a 
                           --ON a.transactionid = p.transactionid 
                            WHERE 
                           p.transactionid = 
                           source.transactionid 
                           --AND a.resultid=0
                            GROUP BY 
                           p.transactionid ) 
                           THEN 1 ELSE 0 END 
WHEN NOT matched THEN 
  INSERT (dayofrecord, 
          sitename, 
          username, 
          connections, 
          bytestransferred) 
  VALUES (source.dayofrecord, 
          source.sitename, 
          source.username, 
          1, 
          source.bytestransferred);

I don't think the Merge is working, even after commenting the JOIN out. If you look at my results and the expected results, it appears the WHEN MATCHED THEN clause is never true, but should be after the first couple Inserts. Again, I'm wondering if the Merge does not know that the Inserts have been done until the Merge statement has completed? — VBStarr, Apr 25 '19 at 13:52
I found the issue in , " ON ( target.dayofrecord = source.dayofrecord AND target.sitename = source.sitename AND target.username = source.username ) " in this line ; May be this column contains the date and time both means it would be like (2019-04-26 10:25:54.297) so, may be this should be fix your issue "ON ( CONVERT(DATE, target.dayofrecord ) = CONVERT(DATE, source.dayofrecord AND target.sitename = source.sitename AND target.username = source.username ) )" — Alchemist, Apr 26 '19 at 05:01

SQL Merge only inserting rows, not updating

1 Answers1