Insert is not checking the where not exist

1

CREATE TABLE [dbo].[FINAL_HS](
[EXTRACT_KEY_ID] [int] NOT NULL,
[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,
CONSTRAINT [PK_FINAL_HS] PRIMARY KEY CLUSTERED
(
[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

INSERT INTO EXTRACT_HS (PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT)
SELECT '103503.0.001','2018','4','1',100.00
INSERT INTO EXTRACT_HS (PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT)
SELECT '103503.0.001','2018','4','1',50.00

SELECT * FROM EXTRACT_HS

UPDATE FINAL_HS
SET BILLED_AMT += A.BILLED_AMT
FROM EXTRACT_HS A
INNER JOIN FINAL_HS B ON A.PROJ_ID = B.PROJ_ID AND A.FY_CD = B.FY_CD AND A.PD_NO = B.PD_NO AND A.SUB_PD_NO = B.SUB_PD_NO

IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO FINAL_HS (EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT)
SELECT EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT FROM EXTRACT_HS
WHERE NOT EXISTS (SELECT * FROM FINAL_HS A WHERE A.PROJ_ID = PROJ_ID AND A.FY_CD = FY_CD AND A.PD_NO = PD_NO AND A.SUB_PD_NO = SUB_PD_NO)
END

I want to insert the first record, but it it already exist update it. There is not data into the FINAL_HS table so the INSERT where not exist is not working as there is no data. When i run it, it tells me that there is a primary key violation.

How do i write this so it will update if record exist or insert.
I am trying to avoid a loop program as there are millions of rows.

Should have one row in FINAL_HS with 150 dollars as the billed_amt

2

You don't need a loop. You don't even need an IF.

UPDATE FINAL_HS
SET BILLED_AMT += A.BILLED_AMT
FROM (
    SELECT PROJ_ID, A.FY_CD, PD_NO, SUB_PD_NO, SUM(BILLED_AMT) AS BILLED_AMT
    FROM EXTRACT_HS
    GROUP BY PROJ_ID, A.FY_CD, PD_NO, SUB_PD_NO
) AS A
INNER JOIN FINAL_HS B ON A.PROJ_ID = B.PROJ_ID AND A.FY_CD = B.FY_CD AND A.PD_NO = B.PD_NO AND A.SUB_PD_NO = B.SUB_PD_NO

INSERT INTO FINAL_HS ( EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT )
SELECT A.EXTRACT_KEY_ID, A.PROJ_ID, A.FY_CD, A.PD_NO, A.SUB_PD_NO, A.BILLED_AMT
FROM (
    SELECT MIN(EXTRACT_KEY_ID) AS EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, SUM(BILLED_AMT) AS BILLED_AMT
    FROM EXTRACT_HS
    GROUP BY PROJ_ID, A.FY_CD, PD_NO, SUB_PD_NO
) AS A
WHERE NOT EXISTS (SELECT * FROM FINAL_HS B WHERE A.PROJ_ID = B.PROJ_ID AND A.FY_CD = B.FY_CD AND A.PD_NO = B.PD_NO AND A.SUB_PD_NO = B.SUB_PD_NO)
3

I copied and paste this and it didn't run - Did it run for you?

4

UPDATE FINAL_HS
SET BILLED_AMT += A.BILLED_AMT
FROM (
SELECT PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, SUM(BILLED_AMT) AS BILLED_AMT
FROM EXTRACT_HS
GROUP BY PROJ_ID, FY_CD, PD_NO, SUB_PD_NO
) AS A
INNER JOIN FINAL_HS B ON A.PROJ_ID = B.PROJ_ID AND A.FY_CD = B.FY_CD AND A.PD_NO = B.PD_NO AND A.SUB_PD_NO = B.SUB_PD_NO

INSERT INTO FINAL_HS ( EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT )
SELECT A.EXTRACT_KEY_ID, A.PROJ_ID, A.FY_CD, A.PD_NO, A.SUB_PD_NO, A.BILLED_AMT
FROM (
SELECT MIN(EXTRACT_KEY_ID) AS EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, SUM(BILLED_AMT) AS BILLED_AMT
FROM EXTRACT_HS
GROUP BY PROJ_ID, FY_CD, PD_NO, SUB_PD_NO
) AS A
WHERE NOT EXISTS (SELECT * FROM FINAL_HS B WHERE A.PROJ_ID = B.PROJ_ID AND A.FY_CD = B.FY_CD AND A.PD_NO = B.PD_NO AND A.SUB_PD_NO = B.SUB_PD_NO)

SELECT * FROM FINAL_HS

I will try it now thanks

5

I actually don't need to do the update, just do the group by before i insert.
Great help thanks

6

I didn't have an EXTRACT_HS table, so I couldn't run it.

I wasn't sure what the EXTRACT_KEY_ID was -- since you didn't say -- but I think I made a reasonable guess as to how to handle it.

7

It worked ...thank you. I couldn't figure it out. I appreciate yr help

8

Your Group works, but then i wanted to see if i could use the MERGE statement as that seems like an easier code to write, but that also fails, so i am confused why the Update isn't recognizing the data when it is what i want it to check on the four primary keys that are in FINAL_HS

MERGE FINAL_HS T USING EXTRACT_HS S ON (T.PROJ_ID=S.PROJ_ID AND T.FY_CD=S.FY_CD AND T.PD_NO=S.PD_NO AND T.SUB_PD_NO=S.SUB_PD_NO)

WHEN MATCHED THEN
UPDATE SET BILLED_AMT+=S.BILLED_AMT

WHEN NOT MATCHED BY TARGET
THEN
INSERT (EXTRACT_KEY_ID, PROJ_ID, FY_CD, PD_NO, SUB_PD_NO, BILLED_AMT)
VALUES (S.EXTRACT_KEY_ID,S.PROJ_ID, S.FY_CD, S.PD_NO, S.SUB_PD_NO, S.BILLED_AMT);

Get same message
Violation of PRIMARY KEY constraint 'PK_FINAL_HS'. Cannot insert duplicate key in object 'dbo.FINAL_HS'. The duplicate key value is (103503.0.001, 2018, 4, 1).

CREATE TABLE [dbo].[EXTRACT_HS](
[EXTRACT_KEY_ID] [int] IDENTITY(1,1) NOT NULL,
[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
) ON [PRIMARY]
GO

9

There could be multiple rows in the EXTRACT_HS table for the same key of ( [PROJ_ID], [FY_CD], [PD_NO], [SUB_PD_NO] ).

That's why I summed the rows prior to INSERT.

For now, I would stick with UPDATE and INSERT. I think MERGE is likely to perform worse, based on my experience. MERGE is also more difficult to understand, especially until you've used it enough to get familiar with the syntax of structure of it.

10

Thanks Scott