Create a field incremental counter

• DerbyNeal

  Hall of Fame

  Points: 3854

  More actions

  September 8, 2011 at 4:43 am

  #247598

  I have a SQL 2005 table (Table_A). One field (File_Name) may contain many instances of the same value. I need an additional field (Instance) which acts as a counter. What is the easiest way to achieve this? Each record is to be inserted individually 'one-at-a-time'.

  Example:

  Before

  File_Name

  Abc_Charlie

  Abc_Neal

  Abc_Neal

  Abc_Tom

  Abc_Tom

  Def_Ed

  Def_Ed

  Ghi_Julie

  Ghi_Ian

  Ghi_Ian

  Ghi_Darcy

  After

  File_Name Instance

  Abc_Charlie1

  Abc_Neal 1

  Abc_Neal 2

  Abc_Tom 1

  Abc_Tom 2

  Def_Ed1

  Def_Ed2

  Ghi_Julie 1

  Ghi_Ian1

  Ghi_Ian2

  Ghi_Ian3

  Ghi_Darcy1

  Any ideas? Thanks in advance,

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 8, 2011 at 5:55 am

  #1378469

  my suggestion: don't store a total in the table. create a view which calculates the total on demand instead...that way it will always be accurate, and you don't need a trigger or any otehr process to "fix" the totals every time one row is added,edited or deleted.

  /*File_NameTheCount

  Abc_Charlie1

  Abc_Neal2

  Abc_Tom2

  Def_Ed2

  Ghi_Darcy1

  Ghi_Ian2

  Ghi_Julie1

  */

  With MySampleData ([File_Name])

  AS

  (

  SELECT 'Abc_Charlie' UNION ALL

  SELECT 'Abc_Neal' UNION ALL

  SELECT 'Abc_Neal' UNION ALL

  SELECT 'Abc_Tom' UNION ALL

  SELECT 'Abc_Tom' UNION ALL

  SELECT 'Def_Ed' UNION ALL

  SELECT 'Def_Ed' UNION ALL

  SELECT 'Ghi_Julie' UNION ALL

  SELECT 'Ghi_Ian' UNION ALL

  SELECT 'Ghi_Ian' UNION ALL

  SELECT 'Ghi_Darcy')

  SELECT [File_Name],

  COUNT([File_Name]) As TheCount

  FROM MySampleData

  GROUP BY [File_Name]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737892

  More actions

  September 8, 2011 at 8:29 am

  #1378578

  I'd agree with Lowell. There isn't a way to do this on a field, you'd have to build a UDF or code that would check to see if the next insert needed an increment.