SQL Server Performance

Insert SP

Discussion in 'SQL Server 2008 General Developer Questions' started by rizwanmgm, Jan 25, 2011.

  1. rizwanmgm New Member

    Hi All,
    I have a table and fields like and data should be insert like the format below
    APPSER DOCID
    1 1
    2 1
    3 2
    1 2
    2 3
    3 1
    i want to write a insert stored procedure so that user just enter the DOCID and APPSER should come in the above format automatically by stored procedure..i need your help my friends please.
    Thanks & Good Day
    rizwanmgm, Jan 25, 2011
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    Can you please explain a little bit more about the logic how APPSER values shall be determined? At first it looks as if it is increasing per DOCID, but then something looks wrong in your sample.
    FrankKalis, Jan 25, 2011
    #3

  3. rizwanmgm New Member

    Hi FrankKalis.
    actually the logic is by DOCID.when user provide the docid the the SP should check the max value and insert record.also please see the structure how the table values should look.the APPSER is depend upon DOCID.i hope u understand.and waiting for your reply.
    APPSER DOCID
    1 1
    2 1
    3 1
    1 2
    2 2
    3 2
    1 3
    2 3
    3 3
    Thanks
    rizwanmgm, Jan 25, 2011
    #4

  4. FrankKalis Moderator

    I guess I would make this a presentation issue. You could have some IDENTITY column in your table, and whenever you have to return the data to a client use something like this:
    DECLARE @t TABLE (tableID int IDENTITY, DOCID int)

    INSERT INTO @t( DOCID ) SELECT 1
    INSERT INTO @t( DOCID ) SELECT 1
    INSERT INTO @t( DOCID ) SELECT 1
    INSERT INTO @t( DOCID ) SELECT 1
    INSERT INTO @t( DOCID ) SELECT 1
    INSERT INTO @t( DOCID ) SELECT 2
    INSERT INTO @t( DOCID ) SELECT 2

    SELECT
    x.APPSER,
    x.DOCID
    FROM
    (
    SELECT
    ROW_NUMBER() OVER(PARTITION BY DOCID ORDER BY [@t].tableID) AS APPSER,
    [@t].DOCID
    FROM
    @t) x
    APPSER DOCID
    1 1
    2 1
    3 1
    4 1
    5 1
    1 2
    2 2

    (7 row(s) affected)

    All other approaches that try to determine the next value for APPSER and store it permanently in the table will more or less have an impact on concurrency and performance.
    FrankKalis, Jan 25, 2011
    #5

Share This Page