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);