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
ENDI'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
Aaron Tomosky
Hard to look at the .txt on my phone, but are there nulls hiding in there? Those will usually cause that sort of problem
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
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.
Can I get an example of the code to fix msg 8672
Hi,
What is the error message?
And you should post this in another question.
Regards
David
What is the error message?
And you should post this in another question.
Regards
David
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.
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.