If exists Update... else insert (1 Viewer)

[bobdole]

I have spent countless hours researching this issue and have found nothing that works.

I have data in one table that has duplicate records... they aren't duplicate in the usual sense though, I need to sum the quantity field in those duplicates and make it one record.

For example,
PK Fields - Store#, Item#
non-PK Fields - Description, Qty

Right now, the way the data is available (via excel files) there are multiple records of the same item from the same store with different Qty's that need to be summed together to get the true value.

In MS SQL I could very easily write some T-SQL that does something that basically says... If exists Update... else insert

I can not find ANYTHING that can get this done.

I will love forever the person that can help me with this.

 

[gemma-the-husky]

try createing a (normal select query) sum total query based on your table, which draws together the duplicated items. you will then get a table consisting of totals for all the items, with a single row per item. i think you could probably use this query (table) to generate a new table with one record per item (make table query)

the only thing is , is that you will lose any differences between records in the duplicated ones. (ie fields that do not carry the same info)

hope this helps

if its a one-off, do it by hand - if you need to automate it for genral use it probably wond be too hard.

 

[bobdole]

Thanks for the advice.

I have the select now that does this and it appears to be working. Would I just do this?

INSERT INTO Table1 (Value1,value2,value3)
Values (the select you recommended)

is that the correct approach?

 

[bobdole]

Correction: The select I have is not working

SELECT Temp.[Str #], Temp.M, Temp.[Street Address], Temp.City, Temp.State, Temp.[Zip Code], Temp.[Phone Nbr], Temp.Description, Temp.[Item #], Temp.YTD, Temp.OH, Sum(Temp.Qty) As SumQty
FROM Temp
GROUP BY Temp.[Str #], Temp.[Item #], Temp.Description, Temp.M, Temp.[Street Address], Temp.City, Temp.State, Temp.[Zip Code], Temp.[Phone Nbr], Temp.YTD, Temp.OH;

 

[ajetrumpet]

Bob, the SELECT query you've written will give you every record you asked for with the SUM of the entire qty field in every record. Thus, if you have 50,000 records, you will get "$0000.00" as a total 50,000 times.

If you've spent hours on it already, I would just do it manually (so to speak). Thinking about it, I'm not sure if there are any shortcuts for this, but you could write maybe two different queries to bring your data together once and for all. You could find the duplicates with the HAVING COUNT()>1 criteria, sum the totals of the similar records, and then append only one row from each item line (with the totaled amount with it) to your original table.

 

[Pat Hartman]

What isn't working about the select? When you are aggregating data, you need to eliminate the columns that would prevent the aggregation. For example, if you wanted to sum data to a state, you would need to eliminate city and zip as selected data fields.