troubleshooting Question

Merge statement reporting an error with multiple rows existing

Avatar of wint100
wint100Flag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
6 Comments1 Solution3990 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
David Todd
Senior Database Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros