SQLTeam.com | Weblogs | Forums

Testing an incremental load


#1

I have a table named output and it has 4 columns. Columns are period, index, scenario and run id.3 set of load happens.
For first load all data comes in and run id is set as 1.
Second load new data comes in and run id becomes 2
Third load for different period the load happen and period column is loaded for different period. The run id reset to 1

I need a query which check for each load. It should check if the data exist in table and if exist then it should not do anything if not exist in table it run id should increment 1. Also if the load happens for different period then the run id should reset to 1.

Please help!


#2

It is much easy for us to help you, if you provide some table structure/s (DDL)
Because , saying in words ...

probably I would do like this:
step1 - take the most recent record- (it is enough to get one) , so that will know if it is 1 or 2

   DECLARE @i_id AS INT
   SELECT TOP(1) @i_id = yourID .... FROM X  ORDER BY dataLoad DESC

step2 -base on this , you should change the value to be 2 or 1

step3 - I will use MERGE to do insert/update

ps: this is most like a draft, not having all the information about this. Like : what is the primary key or the combination of columns , so that you check if a data exists or not.