CODEWITHSUNDEEP
asp.netsql-servervb.netsql-server-2008
Touchy Vivace
Touchy Vivace

Reputation: 304

Auto Increment Reseed when another ID has been changed on Insert Query

I Have a two ID that can can be a primary key 

SID DEFECT_ID
  1     1
  1     2
  1     3
  1     4
  1     5

DEFECT_ID is auto increment SID is static and can be another value like 1 or 2 or 3

I need to reseed when sid is another value like this

SID DEFECT_ID
  1     1
  1     2
  1     3
  1     4
  1     5
  2     1
  2     2
  2     3
  3     1
  3     2
  1     6
  1     7

can it be setting in sql server ?

Upvotes: 1

Views: 87

Answers (2)

Vivek S.
Vivek S.

Reputation: 21915

You can use OVER Clause(T-SQL) to get the order number of each ID in your table

SELECT ID
    ,row_number() OVER (
        PARTITION BY ID ORDER BY id ASC
        ) value
FROM table_name

Upvotes: 1

modal_dialog
modal_dialog

Reputation: 743

There's probably a way to do what you're asking, but I have a feeling you might be making it more complicated than it needs to be.

Typically you don't try to "re-seed" like that for identity fields. Instead you end up with output like this:

SID DEFECT_ID
  1     1
  1     2
  1     3
  1     4
  1     5
  2     6
  2     7
  2     8
  3     9
  3     10
  1     11
  1     12

This is how relational databases generally do it and trying to "go against the grain" simply to have the numbers restart for each unique SID is going to be difficult and error-prone.

Upvotes: 1

Related Questions