SQL Server :: Need Help with the Insert Query

Posted on 2012-08-23
Last Modified: 2012-08-23
Hi Experts,

I  have a  table as follows

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.
Question by:B_Pati
    1 Comment
    LVL 18

    Accepted Solution

    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.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now