auto increment and update required field.

Posted by Abhi_patil under Sql Server on 5/6/2010 | Views : 4234 | Status : [Member] | Replies : 4
hi friends..

suppose i have table with col. SrNo, Name, Rank.

SrNo column has set as primary key column and autoincrement set is on.

suppose there are 5 records sequentially i.e
1 abc 1
2 def 2
3 ddd 3
4 eee 4
5 rrr 5

now if i want to insert a new record as pqr and rank is 3, then it should be added to 3rd place in table and rank of next records should be incremented automatically by 1.

i.e new o/p should be
1 abc 1
2 def 2
3 pqr 3
4 ddd 4
5 eee 5
6 rrr 5

-------

please help me..how should my table structure for that and what should be query to insert new record.




Responses

Posted by: Vuyiswamb on: 5/6/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Unfortunately Identity does not allow that. the only way to do this is that when you do an insert you do it in the #temp table and truncate the table , by truncating the table it will start counting from 1, then when you do the inserts they will match what you want

Thank you for posting at Dotnetfunda
[Administrator]

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 5/7/2010 [Member] [MVP] Silver

Up
0
Down
Hi Abhi_Patil

Greetings

In your scenario, The column SrNo is a Primary Key.

The already existing records (SrNo: 3,4,5), If These records referred by the Child table. What will happen ? - Inconsistency of Data

As Vuyiswamb replied, Identity does not allow.

And You can't Truncate the table which is referred by other (You have to break the relationship b/w the tables applied and then you can Truncate and You have to re-apply the constraints again)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Er.deepakdalal on: 5/7/2010 [Member] Starter

Up
0
Down
Hi Abhi_patil

According to Mr.Vuyiswamb identity does not allow but there is a way, according to me you first find out the last number of record in your table after that increment it by one then pass this value to your insert query so every time when you do this your record is increment by one as per your last record and you can do this all by in a single query by using join query.

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Man2vb on: 5/14/2010 [Member] Starter

Up
0
Down
dont use primary key as identity if u want to get sr number then get then maximum value then then do MAX(ID) + 1 thats it enjoy.......

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response