Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to insert scope_identity from parent table into the child table using SSIS

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

  • Monday, January 07, 2013 4:30 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, January 07, 2013 4:39 PM
     
     
    Sign In to Vote
    0

    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:45 PM
     
     
    Sign In to Vote
    0

    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 5:01 PM
     
     
    Sign In to Vote
    0

    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:02 PM
     
     Answered
    Sign In to Vote
    0

    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:14 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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).