Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2012-02-20 : 07:19:03
|
| Hi,Is it possible to have an auto incramneting identity column with criteria.e.g.I have table 1iddesctable 2idfkTable1Iddescid2I want id2 to be based on groups of fkTable1IdHope this makes some sense!So if I do the following inserts and deletes into table 2InsertNULL,1,'some text',NULLNULL,1,'some text',NULLNULL,1,'some text',NULLNULL,1,'some text',NULLDELETE from table 2 where Id 14Insert into table 2InsertNULL,2,'some text',NULLNULL,1,'some text',NULLI end up with2,1,'some text',23,1,'some text',35,2,'some text',16,1,'some text',5 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-20 : 11:39:57
|
| sorry didnt get that. can you explain how you hgot 1 and 5 for last two rows?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2012-02-21 : 04:28:37
|
| I want my second identity column to be based on fkTable1Idthere were 4 inserts for forign key 1 then two deletes and a final insert resulting in an id of 5there was one insert for forign key 2 resulting in an id of 1Sorry it is hard to explain! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 14:23:26
|
| looks like what you need is to use ROW_NUMBER() OVER (PARTITION BY fkTable1Id ORDER BY id) and update id2 with it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Auto increment ident column with criteria