none
working with 1+ tables in single stored procedure.

    Question

  • Hi , 

    i know to use one table in single stored procedure but i want to know that how i can insert values in 2 tables using single stored procedure in such a way when it insert the the row in 1st table it should get the ID of that row and then insert remaining data with that ID in 2nd table.

    Please provide code , reference tutorials , articles etc

    Thanks

    Wednesday, February 29, 2012 7:44 PM

Answers

  • A bare bones start might look something like this:
     

    -- -------------------------------
    --  Be sure to add error catching
    --  and any transaction framing
    --  as needed
    -- -------------------------------
    create procedure dbo.yourName
      @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx,
      @parm_b1 xxx,
      @parm_b2 xxx,
      ...
      @parm_bN xxx
    as
    --
    insert into table1
    ( <column_List>
    ) values
    ( @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx
    );
    --
    insert into table2
    ( <column_List>
    ) values
    ( scope_Identity(),
      @parm_b1,
      @parm_b2,
      ...
      @parm_bN
    );
    go

    • Edited by Kent WaldropModerator Wednesday, February 29, 2012 8:14 PM
    • Proposed as answer by Peja Tao Thursday, March 01, 2012 8:34 AM
    • Marked as answer by Shan Khan Wednesday, August 29, 2012 8:35 AM
    Wednesday, February 29, 2012 7:56 PM
  • It is the scope_Identity() function that handles that; however, you must include the ID column in the column list.  Note that this makes the insert for table2 slightly different than the insert for table1.
    Wednesday, August 29, 2012 11:54 AM

All replies

  • A bare bones start might look something like this:
     

    -- -------------------------------
    --  Be sure to add error catching
    --  and any transaction framing
    --  as needed
    -- -------------------------------
    create procedure dbo.yourName
      @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx,
      @parm_b1 xxx,
      @parm_b2 xxx,
      ...
      @parm_bN xxx
    as
    --
    insert into table1
    ( <column_List>
    ) values
    ( @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx
    );
    --
    insert into table2
    ( <column_List>
    ) values
    ( scope_Identity(),
      @parm_b1,
      @parm_b2,
      ...
      @parm_bN
    );
    go

    • Edited by Kent WaldropModerator Wednesday, February 29, 2012 8:14 PM
    • Proposed as answer by Peja Tao Thursday, March 01, 2012 8:34 AM
    • Marked as answer by Shan Khan Wednesday, August 29, 2012 8:35 AM
    Wednesday, February 29, 2012 7:56 PM
  • Hi Shan,

    If there is no foreign key relationship specified, you can achieve this with OUTPUT clause.

    CREATE PROCEDURE ProcedureName
    (
    	@Name	NVARCHAR(50),
    	@Price	DECIMAL(10,2)
    )
    AS
    INSERT INTO Table1 (Name)
    OUTPUT INSERTED.IDColumn, @Price INTO Table2
    VALUES (@Name)


    - Vishal

    SqlAndMe.com

    Thursday, March 01, 2012 4:11 AM
  • A bare bones start might look something like this:
     

    -- -------------------------------
    --  Be sure to add error catching
    --  and any transaction framing
    --  as needed
    -- -------------------------------
    create procedure dbo.yourName
      @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx,
      @parm_b1 xxx,
      @parm_b2 xxx,
      ...
      @parm_bN xxx
    as
    --
    insert into table1
    ( <column_List>
    ) values
    ( @parm_a1 xxx,  
      @parm_a2 xxx,
      ...
      @parm_aN xxx
    );
    --
    insert into table2
    ( <column_List>
    ) values
    ( scope_Identity(),
      @parm_b1,
      @parm_b2,
      ...
      @parm_bN
    );
    go

    Thanks but how i can get the ID of inserted row from table 1 and insert that ID in table 2?
    Thursday, March 01, 2012 10:52 AM
  • It is the scope_Identity() function that handles that; however, you must include the ID column in the column list.  Note that this makes the insert for table2 slightly different than the insert for table1.
    Wednesday, August 29, 2012 11:54 AM