none
How to insert scope_identity from parent table into the child table using SSIS

    Question

  • Hi,

    I have a parent table tbl1 which has a primary key. I want to insert data into tbl1 and then the PrimaryKey is inserted with other details into child table which is tbl2.

    Example

    tbl1

    ID    Name

    1     Profile

    2     Test

    3     New

    tbl2

    ID       tbl1ID    Details

    100      1          a new profile added

    101      2          testing details

    102      3          new details added

    So If id 4 is added in tbl1, I want ot insert data against the id 4 in tbl2 and so on...

    I want to achieve the above using SSIS.

    Thanks in advance for your help.

    Aash


    Aash

    Monday, January 07, 2013 4:30 PM

Answers

  • Aash,

    Unfortunately, I havnt got any example. But Let me try to explain in detail.

    step 1. First excecute sql task : This would insert rows in parent table. Just when insert statement finishes, you should run select scope_indentity() to capture the id generated. Save this result set into a variable.

    step2: Second execute sql task: the query here would use the variable that the holds the value of the id generated in parent table.

    Thanks,


    hsbal

    • Marked as answer by Aash Patel Friday, January 11, 2013 10:41 AM
    Monday, January 07, 2013 5:02 PM
  • 1- use a script task

    2- add a Parameter as input to pass the values of the NAME column

    3- Set the result set to single row and add a variable that will play the role of the ID in the RESULT SET Lets call it MyID

    3- with in it use

    DECLARE @tblName NvarChar(50)
    SET @tblName = ?
    INSERT INTO dbo.TblABC (Name) 
    VALUES (@tblName)
    SELECT SCOPE_IDENTITY()

    4-now use  MyID in the next option

    5- a loop or a DFT that will insert the  DETAIL of the records in the second table

    6- good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).


    Monday, January 07, 2013 5:14 PM

All replies

  • Hi,

    To achieve this, in execute sql task you can use select scope_identity() function. You can save the value of this into variable. Later insert the value of the id (from variable) into child table.

    Thanks,


    hsbal

    Monday, January 07, 2013 4:39 PM
  • Hi Harry.

    Have you got an example which I can refer to. I have done the scope_identity in SQL. Wanted to know how to do it in SSIS.

    Thanks,

    Aash


    Aash

    Monday, January 07, 2013 4:45 PM
  • Insert your header table using a script like ....

    INSERT

    INTO dbo.TblABC (Name)

    VALUES ('Profile')

    SELECT @ID = SCOPE_IDENTITY()The @ID is a SSIS variable that will be used in the LOOP for your Detail table , you can use it in a derived column


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Monday, January 07, 2013 5:01 PM
  • Aash,

    Unfortunately, I havnt got any example. But Let me try to explain in detail.

    step 1. First excecute sql task : This would insert rows in parent table. Just when insert statement finishes, you should run select scope_indentity() to capture the id generated. Save this result set into a variable.

    step2: Second execute sql task: the query here would use the variable that the holds the value of the id generated in parent table.

    Thanks,


    hsbal

    • Marked as answer by Aash Patel Friday, January 11, 2013 10:41 AM
    Monday, January 07, 2013 5:02 PM
  • 1- use a script task

    2- add a Parameter as input to pass the values of the NAME column

    3- Set the result set to single row and add a variable that will play the role of the ID in the RESULT SET Lets call it MyID

    3- with in it use

    DECLARE @tblName NvarChar(50)
    SET @tblName = ?
    INSERT INTO dbo.TblABC (Name) 
    VALUES (@tblName)
    SELECT SCOPE_IDENTITY()

    4-now use  MyID in the next option

    5- a loop or a DFT that will insert the  DETAIL of the records in the second table

    6- good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).


    Monday, January 07, 2013 5:14 PM