• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

SQL Server :: Need Help with the Insert Query

Hi Experts,

I  have a  table as follows

ID  NAME  SeqNo
1     aaa        0
2     bbb       0
1     aaa        1
1     aaa        2
2    bbb        1

Upon  inserting a new record  into the table  with   id already existing in the Table
The Sequence number should be incremented by 1.
Ex: upon adding this row

2   bbb
sequence number now should be
2   bbb  2.  (just like incrementing the seq no as an identity column.)

Can i do it like this.
tempvar = select Max(seqno)  where id = @id.
and doing TempVar + 1 on inserting will increment the column .

Please advice if there is any better approach to do this.
0
B_Pati
Asked:
B_Pati
1 Solution
 
CluskittCommented:
INSERT INTO MyTable
SELECT ID, NAME, MAX(SeqNo)+1
FROM MyTable
WHERE ID=2 AND NAME='bbb'

This, however, won't allow for first time inserts. That is, if you try to insert:
3     ccc
This will effectively return and insert nothing. If you want to make sure you get first time inserts as well, you should then simply use a variable, which you can use as SQL:
DECLARE NextNum int
SET NextNum=ISNULL(SELECT MAX(SeqNo)+1 FROM MyTable WHERE ID=2 AND NAME='bbb'),1)
INSERT INTO MyTable Values (2,'bbb',NextNum)

replace 2 and bbb with whatever parameters you're using and you should be done.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now