Merge statement reporting an error with multiple rows existing

USE [EnergySuiteDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_ManualMeterReadings_BulkUploadMerge]    Script Date: 12/29/2013 07:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ManualMeterReadings_BulkUploadMerge]
    (
      @MeterID INT 
    )
AS 
    BEGIN
    DECLARE @retValue INT
    declare @dt datetime
    declare @matched INT
        BEGIN TRY
            IF OBJECT_ID('ManualMeterReadings') IS NOT NULL 
                BEGIN
                    BEGIN TRANSACTION MergManualMeterReadingsTable
                    SET NOCOUNT ON;
                    MERGE dbo.ManualMeterReadings AS target
                        USING 
                            ( SELECT    PU.MeterID,
                                PU.MPAN ,
                                PU.MeterReference ,
                                PU.MeterReference2 ,
                                PU.MeterReading ,
                                PU.PeakDemand ,
                                PU.Offset ,
                                PU.PowerFactor ,
                                PU.ReactiveConsumption ,
                                PU.ReadingType ,
                                PU.Consumption ,
                                PU.Target ,
                                PU.TargetDifference ,
                                PU.Timestamp ,
                                PU.ActualkVA ,
                                PU.AvailablekVA ,
                                PU.Barcode 

                              FROM      dbo.tempManualMeterReadings PU
                              WHERE     PU.MeterID = @MeterID
                              GROUP BY  PU.MeterID,
							    PU.MPAN ,
                                PU.MeterReference ,
                                PU.MeterReference2 ,
                                PU.MeterReading ,
                                PU.PeakDemand ,
                                PU.Offset ,
                                PU.PowerFactor ,
                                PU.ReactiveConsumption ,
                                PU.ReadingType ,
                                PU.Consumption ,
                                PU.Target ,
                                PU.TargetDifference ,
                                PU.Timestamp ,
                                PU.ActualkVA ,
                                PU.AvailablekVA ,
                                PU.Barcode 

                            ) AS source (MeterID, MPAN ,
                                MeterReference ,
                                MeterReference2 ,
                                MeterReading ,
                                PeakDemand ,
                                Offset ,
                                PowerFactor ,
                                ReactiveConsumption ,
                                ReadingType ,
                                Consumption ,
                                Target ,
                                TargetDifference ,
                                Timestamp ,
                                ActualkVA ,
                                AvailablekVA ,
                                Barcode  )
                        ON ( (target.MeterID) = (source.MeterID)
                             AND (target.Timestamp) = (source.Timestamp)
                           )
                        WHEN MATCHED 
                            THEN 
        UPDATE                SET
                @dt=source.timestamp,
                Consumption = source.Consumption ,
                MeterReading = source.MeterReading,               
                ReactiveConsumption=source.ReactiveConsumption,
				ActualkVA=source.ActualkVA,
				AvailablekVA=source.AvailablekVA
                        WHEN NOT MATCHED 
                            THEN 
        INSERT  (
                  MeterID, MPAN ,
                                MeterReference ,
                                MeterReference2 ,
                                MeterReading ,
                                PeakDemand ,
                                Offset ,
                                PowerFactor ,
                                ReactiveConsumption ,
                                ReadingType ,
                                Consumption ,
                                Target ,
                                TargetDifference ,
                                Timestamp ,
                                ActualkVA ,
                                AvailablekVA ,
                                Barcode
                )             VALUES
                ( source.MeterID, source.MPAN ,
                                source.MeterReference ,
                                source.MeterReference2 ,
                                source.MeterReading ,
                                source.PeakDemand ,
                                source.Offset ,
                                source.PowerFactor ,
                                source.ReactiveConsumption ,
                                source.ReadingType ,
                                source.Consumption ,
                                source.Target ,
                                source.TargetDifference ,
                                source.Timestamp ,
                                source.ActualkVA ,
                                source.AvailablekVA ,
                                source.Barcode
                );
                    DELETE  PU
                    FROM    dbo.tempManualMeterReadings PU
                    WHERE   PU.MeterID = @MeterID
                    COMMIT TRANSACTION MergManualMeterReadingsTable
                    SET @retValue = 1
                    SELECT @retValue
                END
            ELSE 
                BEGIN
                SET @retValue = -1
                    SELECT  @retValue
                END
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION MergManualMeterReadingsTable
            DECLARE @ErrorMsg VARCHAR(MAX);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
            SET @ErrorMsg = ERROR_MESSAGE();
            SET @ErrorSeverity = ERROR_SEVERITY();
            SET @ErrorState = ERROR_STATE();
        SET @retValue = 1000
        SELECT @retValue
           -- SELECT  0 AS isSuccess
        END CATCH
    END

Open in new window

Hi,

I'm trying to use the attached merge statement to insert or update data to my table. The insert works well but the update always returns an error that multiple rows exist, meaning it suggests I have multiple rows in the tempManualMeterReadings tables where MeterID and Timestamp are the same.

I've attached the source tempManualMeterReadings table data and the target ManualMeterReadings table data. I can't see where the duplication exists, there should be only one match for each row.

Any idea where I'm going wrong here?

[embed=snippet 8258877
Source.txt
Target.txt
LVL 1
wint100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron TomoskyDirector of Solutions ConsultingCommented:
Hard to look at the .txt on my phone, but are there nulls hiding in there? Those will usually cause that sort of problem
David ToddSenior Database AdministratorCommented:
Hi,

I imported your tables into a test database, ran your merge and got this error
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Please run this query:
use ExpertsExchange
go

select 
	MeterID
	, Timestamp
	, count( * )	
from dbo.ManualMeterReadings
group by
	MeterID
	, Timestamp
having
	count( * ) > 1
;

select 
	MeterID
	, Timestamp
	, count( * )	
from dbo.tempManualMeterReadings
group by
	MeterID
	, Timestamp
having
	count( * ) > 1
;

Open in new window

Which produces

MeterID                Timestamp               
---------------------- ----------------------- -----------
43                     2012-10-28 01:30:00.000 2
43                     2013-10-27 01:30:00.000 2
43                     2013-10-27 01:00:00.000 2
43                     2012-10-28 01:00:00.000 2

(4 row(s) affected)

MeterID                Timestamp               
---------------------- ----------------------- -----------
43                     2013-10-27 01:30:00.000 2
43                     2013-10-27 01:00:00.000 2

(2 row(s) affected)

Open in new window

As you can see, you have duplicates in your data.

HTH
  David

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wint100Author Commented:
Hi,

Sorry for the delay. Thanks a lot for this query and pointing out my error in thinking there were no duplicate rows. I've now modified the query to only insert when the data isn't already present, and ignore existing data.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

adrian lujanCommented:
Can I get an example of the code to fix msg 8672
David ToddSenior Database AdministratorCommented:
Hi,

What is the error message?

And you should post this in another question.

Regards
  David
adrian lujanCommented:
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.