Inserting multiple rows using stored procedure

  • Hi All,

    Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

    Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

    [p]The first table is used to store the token starting number and end number.[/p]

    [p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]

    [p]how can i insert that number of records using stored procedure?

    is there for loop to do this task?

    plz help me in this issue?

    thnks in advance[/p]

  • karthimca07 (7/8/2009)


    Hi All,

    Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

    Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

    [p]The first table is used to store the token starting number and end number.[/p]

    [p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]

    [p]how can i insert that number of records using stored procedure?

    is there for loop to do this task?

    plz help me in this issue?

    thnks in advance[/p]

    declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue int

    insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)

    select

    @Transid,

    N,

    @TokenValue,

    'A' -- or whatever your status field should be

    from dbo.Tally

    where N between @TokenStart and @TokenEnd

    If you don't have a tally table, search this site... it's explained and used frequently.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (7/8/2009)


    karthimca07 (7/8/2009)


    Hi All,

    Tab_Token_Master(Transid,Token_starts_from,Token_Ends_To,Token_value)

    Tab_Token_Details(Transid,Token_No,Token_Value,Token_Status)

    [p]The first table is used to store the token starting number and end number.[/p]

    [p]for example (staring number)1 to 100(ending number). so i have to insert token1,token2...token100 in tab_Token_details table.[/p]

    [p]how can i insert that number of records using stored procedure?

    is there for loop to do this task?

    plz help me in this issue?

    thnks in advance[/p]

    declare @Transid int, @TokenStart int, @TokenEnd int, @TokenValue int

    insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)

    select

    @Transid,

    N,

    @TokenValue,

    'A' -- or whatever your status field should be

    from dbo.Tally

    where N between @TokenStart and @TokenEnd

    If you don't have a tally table, search this site... it's explained and used frequently.

    [p]the first table Tab_Token_Master is used to store the token generation and the second table used to store the all generated tokens.[/p]

    Tab_Token_Master

    -----------------------------------------------------------------

    Transid Token_starts_from Token_Ends_To Token_value

    -----------------------------------------------------------------

    trans01 101 105 100

    ------------------------------------------------------------------

    Tab_Token_Details

    -----------------------------------------------------------------

    Transid Token_No Token_Value Token_Status

    -----------------------------------------------------------------

    trans01 101 100 0

    trans01 102 100 1

    trans01 103 100 0

    trans01 104 100 1

    trans01 105 100 1

    -----------------------------------------------------------------

    in the above example data 5 tokens generated. im calling insert statement in a for loop in vb.net. it become very slow. so im looking for stored procedure

  • How do you know what to set token_status to in Tab_Token_Details?

    Is you current process doing an insert into Tab_Token_Master, then looping through and inserting the correct number of rows into Tab_Token_Details or are you reading Tab_Token_Master and then inserting into Tab_Token_Details?

    For the first scenario you could do it in one stored procedure like this:

    CREATE PROCEDURE create_tokens

    (

    @transid VARCHAR(10),

    @token_starts_from SMALLINT,

    @tokens_ends_to SMALLINT,

    @token_value SMALLINT

    )

    AS

    BEGIN TRANSACTION token_master

    INSERT INTO Tab_Token_Master

    (

    transid,

    token_starts_from,

    token_ends_to,

    token_value

    )

    VALUES

    (

    @transid,

    @token_starts_from,

    @token_ends_to,

    @token_value

    )

    COMMIT TRANSACTION token_master

    BEGIN TRANSACTION token_details

    -- uses WayneS's code so you still need to lookup tally table

    insert into dbo.Tab_Token_Details (Transid, Token_No, Token_Value, Token_Status)

    select

    @Transid,

    N,

    @token_value ,

    'A' -- or whatever your status field should be

    from

    dbo.Tally

    where N between @token_starts_from AND @tokens_ends_to

    COMMIT TRANSACTION token_details

    RETURN

    For the second scenario you just need to remove the insertion into Tab_Token_Master

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply