SQLTeam.com | Weblogs | Forums

Merge data is giving me a duplicate

There are two rows 173671.0.003.03 and 173671.0.002.02 that merge into one row
103518.0.005.01 2018 7

When i run the merge it says it errors.
PRIMARY KEY constraint 'PI_1031_CMG'. Cannot insert duplicate key in object 'dbo.LOAD_PROJ_BILL_HS'. The duplicate key value is (103518.0.005.01, 2018, 7, 1).

i thought that when the MERGE runs it reads row one and inserts this into the table, and then when the second row runs it knows it already inserted it and just
updates it but it isn't doing this it is saying it is duplicate.
I was expecting it to do the update on 2nd row making the grand total the 20.00 in billed amt

Any advice ?

CREATE TABLE [dbo].[LOAD_PROJ_BILL_HS](
[PROJ_ID] varchar NOT NULL,
[FY_CD] varchar NOT NULL,
[PD_NO] [smallint] NOT NULL,
[SUB_PD_NO] [smallint] NOT NULL,
[BILLED_AMT] [decimal](17, 2) NOT NULL,
[RTNGE_AMT] [decimal](17, 2) NOT NULL,
[MODIFIED_BY] varchar NOT NULL,
[TIME_STAMP] [datetime] NOT NULL,
[DEL_AMT] [decimal](17, 2) NOT NULL,
[ROWVERSION] [int] NULL,
[BILL_WH_AMT] [decimal](17, 2) NULL,
[BILL_WH_REL_AMT] [decimal](17, 2) NULL,
CONSTRAINT [PI_1031_CMG] PRIMARY KEY NONCLUSTERED
(
[PROJ_ID] ASC,
[FY_CD] ASC,
[PD_NO] ASC,
[SUB_PD_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[STAGE_PROJ_BILL_HS](
[EXTRACT_KEY_ID] [int] IDENTITY(1,1) NOT NULL,
[LOAD_ID] [int] NULL,
[SOURCE_PROJ_ID] varchar NOT NULL,
[SOURCE_FY_CD] varchar NOT NULL,
[SOURCE_PD_NO] [smallint] NOT NULL,
[SOURCE_SUB_PD_NO] [smallint] NOT NULL,
[SOURCE_BILLED_AMT] [decimal](17, 2) NOT NULL,
[SOURCE_RTNGE_AMT] [decimal](17, 2) NOT NULL,
[SOURCE_MODIFIED_BY] varchar NOT NULL,
[SOURCE_TIME_STAMP] [datetime] NOT NULL,
[SOURCE_DEL_AMT] [decimal](17, 2) NOT NULL,
[SOURCE_ROWVERSION] [int] NULL,
[SOURCE_BILL_WH_AMT] [decimal](17, 2) NULL,
[SOURCE_BILL_WH_REL_AMT] [decimal](17, 2) NULL,
[PROJ_ID] varchar NULL,
[FY_CD] varchar NULL,
[PD_NO] [smallint] NULL,
[SUB_PD_NO] [smallint] NULL,
[BILLED_AMT] [decimal](17, 2) NULL,
[RTNGE_AMT] [decimal](17, 2) NULL,
[MODIFIED_BY] varchar NULL,
[TIME_STAMP] [datetime] NULL,
[DEL_AMT] [decimal](17, 2) NULL,
[ROWVERSION] [int] NULL,
[BILL_WH_AMT] [decimal](17, 2) NULL,
[BILL_WH_REL_AMT] [decimal](17, 2) NULL,
[MARKER] varchar NULL,
[GUID_ALL_COLUMNS] varbinary NULL,
[GUID_PRIMARY] AS (hashbytes('SHA2_512',concat('|',[SOURCE_PROJ_ID],[SOURCE_FY_CD],[SOURCE_PD_NO],[SOURCE_SUB_PD_NO])))
) ON [PRIMARY]
GO

INSERT INTO [dbo].[STAGE_PROJ_BILL_HS]
([LOAD_ID]
,[SOURCE_PROJ_ID]
,[SOURCE_FY_CD]
,[SOURCE_PD_NO]
,[SOURCE_SUB_PD_NO]
,[SOURCE_BILLED_AMT]
,[SOURCE_RTNGE_AMT]
,[SOURCE_MODIFIED_BY]
,[SOURCE_TIME_STAMP]
,[SOURCE_DEL_AMT]
,[SOURCE_ROWVERSION]
,[SOURCE_BILL_WH_AMT]
,[SOURCE_BILL_WH_REL_AMT]
,[PROJ_ID]
,[FY_CD]
,[PD_NO]
,[SUB_PD_NO]
,[BILLED_AMT]
,[RTNGE_AMT]
,[MODIFIED_BY]
,[TIME_STAMP]
,[DEL_AMT]
,[ROWVERSION]
,[BILL_WH_AMT]
,[BILL_WH_REL_AMT]
,[MARKER]
,[GUID_ALL_COLUMNS])
VALUES
(1
,'173671.0.002.02'
,'2018'
,'7'
,'1'
,10.00
,0
,'tr'
,getdate()
,'0'
,'0'
,'0'
,'0'
,'103518.0.005.01'
,'2018'
,'7'
,'1'
,10.00
,0
,'tr'
,getdate()
,'0'
,'0'
,'0'
,'0'
,'E'
,0
)
GO

INSERT INTO [dbo].[STAGE_PROJ_BILL_HS]
([LOAD_ID]
,[SOURCE_PROJ_ID]
,[SOURCE_FY_CD]
,[SOURCE_PD_NO]
,[SOURCE_SUB_PD_NO]
,[SOURCE_BILLED_AMT]
,[SOURCE_RTNGE_AMT]
,[SOURCE_MODIFIED_BY]
,[SOURCE_TIME_STAMP]
,[SOURCE_DEL_AMT]
,[SOURCE_ROWVERSION]
,[SOURCE_BILL_WH_AMT]
,[SOURCE_BILL_WH_REL_AMT]
,[PROJ_ID]
,[FY_CD]
,[PD_NO]
,[SUB_PD_NO]
,[BILLED_AMT]
,[RTNGE_AMT]
,[MODIFIED_BY]
,[TIME_STAMP]
,[DEL_AMT]
,[ROWVERSION]
,[BILL_WH_AMT]
,[BILL_WH_REL_AMT]
,[MARKER]
,[GUID_ALL_COLUMNS])
VALUES
(1
,'173671.0.003.03'
,'2018'
,'7'
,'1'
,10.00
,0
,'tr'
,getdate()
,'0'
,'0'
,'0'
,'0'
,'103518.0.005.01'
,'2018'
,'7'
,'1'
,10.00
,0
,'tr'
,getdate()
,'0'
,'0'
,'0'
,'0'
,'E'
,0
)
GO

Merge into CP2VISDEV_TARGET.dbo.LOAD_PROJ_BILL_HS as target1
using(select * from STAGE_PROJ_BILL_HS ) as tab2
on target1.PROJ_ID=tab2.PROJ_ID and
target1.FY_CD=tab2.FY_CD and
target1.PD_NO=tab2.PD_NO and
target1.SUB_PD_NO=tab2.SUB_PD_NO

when matched then
update set
target1.BILLED_AMT=tab2.BILLED_AMT,
target1.RTNGE_AMT=tab2.RTNGE_AMT,
target1.MODIFIED_BY=tab2.MODIFIED_BY,
target1.TIME_STAMP=tab2.TIME_STAMP,
target1.DEL_AMT=tab2.DEL_AMT,
target1.ROWVERSION=tab2.ROWVERSION,
target1.BILL_WH_AMT=tab2.BILL_WH_AMT,
target1.BILL_WH_REL_AMT=tab2.BILL_WH_REL_AMT

when not matched then
insert values(tab2.PROJ_ID,tab2.FY_CD,tab2.PD_NO,tab2.SUB_PD_NO,tab2.BILLED_AMT,tab2.RTNGE_AMT, tab2.MODIFIED_BY, tab2.TIME_STAMP, tab2.DEL_AMT, tab2.ROWVERSION,
tab2.BILL_WH_AMT, tab2.BILL_WH_REL_AMT);

one idea

column Data
1.0.9.1

remove the . using replace
cast as int
then try to compare using int values and MERGE

that's the issue with merge. It can only update 1 row, so your ON clause can only match 1 row. So, either you have more than 1 row in the target table or your source query is generating more than 1 row, based on the fields in the ON clause

No. SQL conceptually works on sets. And SQL typically insures that a given row is only affected one time by a single SQL statement.

You need to remove the duplicates from the SOURCE yourself, then MERGE will work correctly.

1 Like

You should not use merge on such a simple statement, you can better use update and insert.

MERGE (Transact-SQL) - SQL Server | Microsoft Docs

Ok thanks

I didn't know that a merge wasn't that clever, I was hoping not to have to group data first, glad I know know , I shall go back to my original concept.