CODEWITHSUNDEEP
sqlsql-server-2008
Geemoney
Geemoney

Reputation: 11

SQL Server: Insert an aggregate value

I have spent some time researching this but have not found an answer.

So I have one table called WinterInspectionResults, and I have another Table called InspectionResulsts, and what I want to do is insert values from the first table into the second table. The thing is some cases WinterInspectionResults will have multiple entries that correspond to one entry in InspectionResults. So I use the sum() function. The problem is when I try to insert I get the error:

Violation of PRIMARY KEY constraint 'PK_InspectionResults'. 
Cannot insert duplicate key in object 'dbo.InspectionResults'.

So, maybe I don't understand exactly how aggregate functions work. Has anyone else experienced this and/or know how to get around it? Also dbo.InspectionResults has a aggregate key, that is the combination of two foreign keys.

Thanks for the help I figured out what the problem was.

Upvotes: 1

Views: 2716

Answers (2)

Diego
Diego

Reputation: 36126

Im assuming your tables are made of ID and value only. Any other fields you can adjust. What you are missing is the check to not insert the values that are already on InspectionResulsts 

insert into InspectionResulsts (ID, VALUE)
select WI.ID, SUM(WI.VALUE)
from WinterInspectionResults WI
where WI.ID not in (select ID from InspectionResulsts)

Upvotes: 0

Lamak
Lamak

Reputation: 70638

I'm guessing that its not a problem with your SUM, but that you are trying to insert values that already exists on InspectionResults. You should verify that you are not inserting duplicates. Something like this:

INSERT INTO InspectionResults(List of your columns)
SELECT A.*
FROM (  SELECT [Key Columns Of InspectionResults], SUM([Agreggated Column])
        FROM WinterInspectionResults
        GROUP BY [Key Columns Of InspectionResults]) A
LEFT JOIN InspectionResults B
ON A.[Key Columns Of InspectionResults] = B.[Key Columns Of InspectionResults]
WHERE B.[Key Columns Of InspectionResults] IS NULL

Upvotes: 1

Related Questions