We help IT Professionals succeed at work.
Get Started

Merge statement reporting an error with multiple rows existing

3,989 Views
Last Modified: 2014-08-25
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
Comment
Watch Question
Senior Database Administrator
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE