Create a field incremental counter

Question

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,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

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_Tom' UNION ALL

SELECT 'Def_Ed' UNION ALL

SELECT 'Ghi_Julie' 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: 734667 More actions · Answer 2

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.