how to insert into a table or replace it if exists where data comes from separate place and are editable

Question

I can't think of a way to find it's solution so I ask this question,

Let first explain from beginning,

I have this table:

tHoneyHarvest (RecID is PK and auto increment)

  RecID    HarvestDate     HoneyComb   [etc]
-------------------------------------------- 
   1       2017-01-30        5
   2       2017-02-01        3.5
   3       2017-01-25        2
   4       2017-01-30        5

then I get data from this table in this way:

select HarvestDate, HoneyType, Sum (HoneyComb) as TotalCombs 

From tHoneyHarvest 

group by HarvestDate,
order by HarvestDate

and then show it to user and by showing it get a variable Named HoneyWeight for each row of it form user.

now my question is:

I want to these data inserted in a table like this

  tHoneyWeight (RecID is PK and auto increment)

  RecID    HarvestDate     HoneyComb   HoneyWeight
---------------------------------------------------- 
   1       2017-01-30        10           20
   2       2017-02-01        3.5          7
   3       2017-01-25        2            4

it can be done by a simple insert but what if user want to go back and edit honeyComb from tHoneyHarvest or want to edit HoneyWeight.

this cause to inserted duplicate row in tHoneyWeight, and if I want to use update how could I distinguish which row has to be updated

sorry if it is a long question but can't figure it out what should I do

Update:

I find a way to get what I want with help of C#

First I create this procedure

Create Procedure GetTotlCombByHarvDtHTyp

as

Select HarvestDate, HoneyType,SUM(HoneyComb) AS TotalCombs
From tHoneyHarvest
Group BY HarvestDate, HoneyType
Order BY HarvestDate DESC

and then load it data to a datatable in c# and by this loop I edit tHoneyWeight

foreach (DataRow DR in Dtable.Rows)
{
    using (SqlCommand SqlCmd = new SqlCommand())
    {
        SqlCmd.Connection = SqlCon;
        SqlCmd.CommandType = CommandType.StoredProcedure;

        SqlCmd.CommandText = "InsOrUpdtHoneyWt";

        SqlCmd.Parameters.Add("@HarvestDate", SqlDbType.Date).Value = DR["HarvestDate"];
        SqlCmd.Parameters.Add("@HoneyType", SqlDbType.VarChar, 50).Value = DR["HoneyType"];
        SqlCmd.Parameters.Add("@TotalCombs", SqlDbType.Float).Value = DR["TotalCombs"];
        SqlCmd.Parameters.Add("@RV", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        SqlCmd.ExecuteNonQuery();
    }
}

Stored procedure is:

Create Procedure InsOrUpdtHoneyWt

@HarvestDate Date,
@HoneyType VarChar(50),
@TotalCombs Float

as

If NOT Exists (Select * From tHoneyWeight
                     Where (HarvestDate=@HarvestDate AND
                            HoneyType=@HoneyType))
begin
Insert INTO tHoneyWeight 
        (HarvestDate,
         HoneyType,
         TotalCombs)

  Values(@HarvestDate,
         @HoneyType,
         @TotalCombs)

Return 1
end

else

begin
Update tHoneyWeight
       Set TotalCombs=@TotalCombs

Where (HarvestDate=@HarvestDate AND
       HoneyType=@HoneyType)

end

And then I deleted extra row that remain in tHoneyWeight because of delete or update in tHonetHarvest with my last question:

I go this long way because I don't know the better way and I'm sure this gonna cost me a lot of reducing performance as the number of rows increase

Google for `UPDATE SQL command` ... I think you are just looking to do an _update_ of your table. Use the `WHERE` clause to identify the record you want to change. — Tim Biegeleisen, Jan 31 '17 at 08:56
If this is a copy that displays live data, why not use a view? — cloudsafe, Jan 31 '17 at 09:05
@TimBiegeleisen the HoneyWeight assign according to TotalCombs and then (grouped HarvestDate, and TotalCombs And HoneyWeight) Inserted to a table, But what if another HarvestDate and Honeycomb inserted by user how to add this single row in table tHoneyWeight, or how to edit this table if some combes moved to another Date by user ... — SaraniO, Jan 31 '17 at 10:21

score 3 · Answer 1 · edited Jan 31 '17 at 09:31

3

First create a new Column " HoneyWeight" which can have Null value. Then as mentioned by Tim above use-

Update tHoneyHarvest
SET HoneyWeight = input_value
WHERE RecID = (1/2/3...);

edited Jan 31 '17 at 09:31

ATC

907
1
9
14

answered Jan 31 '17 at 09:04

FS_aspirer

83
1
12

how to insert into a table or replace it if exists where data comes from separate place and are editable

1 Answers1