CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Jose Luis
Jose Luis

Reputation: 71

Modify data entry in a column with IDENTITY property

I need to create a table named "PROCESS", its primary key is composed of twocolumns (IdOffice, IdProcess), the problem is that I need the "IdProcess" contains the IDENTITY property and increase one by one but depending on the value of field "IdOffice" as follows:

The "IdProcess" must be increased one by one if the "IdOffice" is the same, so I put a picture for more clearly:

enter link description here

how I do this?

Thanks for your help.

Upvotes: 1

Views: 72

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

I would also suggest that you have a primary key for this table that is an identity. Then treat the IdOffice and IdProcess as data in the row. If you do this, then Aaron's suggestion of calculating the IdProcess at query time makes much more sense.

Upvotes: 0

anon
anon

Reputation:

You can't do this with IDENTITY. You'll need to have a separate column. The problem is that this can be very problematic to maintain. If you have data like this:

IdOffice  IdProcess
--------- ----------
1         1
1         2
1         3
2         1 
2         2

What happens when you delete the second row? Does the IdProcess need to be re-calculated for all values of IdOffice = 1?

Assuming you have some other column to order by, you can always generate this number at query time rather than pay the cost of storing it and dealing with maintenance as the table changes. 

SELECT IdOffice, IdProcess = ROW_NUMBER() OVER 
  (PARTITION BY IdOffice ORDER BY DateCreated)
 FROM  dbo.YourTable
 ORDER BY IdOffice, IdProcess;

Upvotes: 4

Related Questions