title: Updating field sequentially while inserting data in SQL
body: <p>I have a scenario where in I want a field in a table to be incremented sequentially. 
  Suppose I have a table Test, with columns TestID, TestResult1,2 etc.. and TestCount.
  I have data bulk inserted into the table. Some of the records may be retests, which means new data to be inserted matches existing records in the table, Test Count should be updated.Matching is done on TestID</p>
  
  <p>If the table is as follows:</p>
  
  <pre><code>TestID TestResult1 TestResult2.. TestCount 
  12         1         1             1
  12         2          2            2
  13         4         1             1
  </code></pre>
  
  <p>Data to be inserted in </p>
  
  <pre><code>TestID TestResult1 TestResult2.. 
  12         3         5             
  12         2          2    
  </code></pre>
  
  <p>The table should be updated as </p>
  
  <pre><code>TestID TestResult1 TestResult2.. TestCount 
  12         1         1             1
  12         2         2             2
  13         4         1             1
  12         3         5             3
  12         2         2             4
  </code></pre>
  
  <p>I tried adding a trigger on the table to update the TestCount Counting the number of records that matches. But it was updating the table as follows</p>
  
  <pre><code>TestID TestResult1 TestResult2.. TestCount 
  12         1         1             1
  12         2         2             2
  13         4         1             1
  12         3         5             3
  12         2         2             3
  
  CREATE TRIGGER trgTestCount
  on Test
  AFTER INSERT 
  AS 
  Update  g
  Set TestCount= (Select Count(*)+1  from Test g join INSERTED g1 where g.TestID=g1.TestID )
  from Test g 
  </code></pre>
  
  <p>This is a SSIS package and I use a dataflow task to load data from STg table to test table.
  Can you tell me what I am doing wrong here?</p>