Help updating or inserting into a table

[cdiwindows]

I have a table with 3 columns Store/Date/Sales The table holds the "record Sales" for each store and the date they set the record. I have been asked to add to this table every time the store sets a new record. If a store beats their record another row is added to the table, not just overwritten.

Most of my time in SQL has been running reports, not updating the data, so this is kind of new to me.

Example of data
1050 12/07/2004 12:00:00 AM 13025.4
1060 02/14/2010 12:00:00 AM 12000
1061 11/12/2005 12:00:00 AM 32023.14
1062 02/14/2010 12:00:00 AM 37802.72
1092 02/14/2009 12:00:00 AM 25251.66

If store 1060 has sales of 12050.00 on 1/5/12 then 13234.44 on 1/18/2012 then the file would look like:

1050 12/07/2004 12:00:00 AM 13025.4
1060 02/14/2010 12:00:00 AM 12000
1060 01/05/2012 12:00:00 AM 12050
1060 01/18/2010 12:00:00 AM 13234.44
1061 11/12/2005 12:00:00 AM 32023.14
1062 02/14/2010 12:00:00 AM 37802.729999999996
1092 02/14/2009 12:00:00 AM 25251.66

I currently have a report that is run daily, sometimes multiple times, that I could include the update in but only update the record file if it does not already exist. Our Sales file that the original data comes from is replicated to our server from an outside vendor, so the sales report may be run multiple times to verify all the data is replicated to us before the report is published.

Thanks for your input.

 

[gmmastros]

Do you have this data stored in another table also? Like a daily sales table?

The reason I ask is because it would probably be better to write a trigger that automatically inserts the "sales record" information in to the RecordSales table whenever data in the DailySales table is inserted.

The way it would work is this....

When you write a trigger, you specify whether it is an insert trigger, update trigger, and/or delete trigger. In your case, you would only need an insert trigger.

Whenever data is inserted in to the table (DailySales in this case), the trigger code is executed automatically by the database engine. As long as the trigger is working properly, you'll never have to worry about inserting new data in to the RecordSales table. More specifically, the trigger will run no matter how the table is inserted in to. Right now, the data is getting replicated from an outside vendor. It wouldn't be too difficult to write code to insert new rows in to the table. However, if (6 months from now) you add another way for the data to get inserted in to this table, you won't need to worry about adding the code that inserts to the RecordSales table because the trigger is going to do that for you automatically.

On the other hand, if you don't already have another table with this data in it, then you shouldn't use the trigger approach.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cdiwindows]

George,
There is another table that includes the sales data that this would be coming from. The data in our Sales table will always come to us via replication from our vendor. We have 73 stores that have data pushed to us.

So a trigger should work.

 

[gmmastros]

In the code I show below, I make several assumptions. I doubt that I guessed everything exactly, so you will need to adjust the code accordingly.

I assume:

1. DailySales table that stores the data every day.
2. RecordSales table that stored the data whenever a store breaks their record.
3. Stores is a list of your stores with 1 row per store.

I also make assumptions about column names.

The trigger code should look something like this:

Create  Trigger InsertRecordSales On DailySales
For Insert
As
Begin
  SET NOCOUNT ON

  ;With BestSales As
  (
  Select   StoreId,
           Max(SalesAmount) As MaxSalesAmount
  From     RecordSales
  Group By StoreId
  )
  Insert
  Into   RecordSales(StoreId, SalesDate, SalesAmount)
  Select I.StoreId,
         I.SalesDate,
         I.SalesAmount
  From   Stores 
         Inner Join Inserted I
           On Stores.StoreId = I.StoreId
         Left Join BestSales 
           On Stores.StoreId = BestSales.StoreId
  Where	I.SalesAmount > Coalesce(BestSales.MaxSalesAmount, 0)
End

Now... please understand that there are some relative complex things going on here. First, there is a common table expression at the top of the code. This exists so that we can get the StoreId and it's max sales value. We do this because we only want to insert a new row in to the table if the currently inserted value is greater than the existing max value.

Notice the Inserted table. This table exists during the trigger and mimics the table structure of the DailySales table (because this is the table that the trigger is written for).

When you use a trigger, you'll want to make sure that you test this really well.

Also, you should understand that trigger code can slow down your inserts. Given the code I show, assuming you have indexes on your tables, you are not likely to notice any performance degradation. I would recommend that you monitor the situation for a while to make sure the daily loading of data is not affected.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom