none
How to insert values into multiple tables which is related with primary and foreign keys

    Question

  • I have four tables Table 1,2,3,4
    the table 1  has primary key and table 2 has foreign keys from 1 and 3 .And table 2 primary key is foreign key to table 4 this is the relationship between tables 

    I have  to insert values into these tables such that save values in table 1 and table 3 and get thier primary key value of the rows inserted and using these foreign keys insert values into table 2 and then get the primary key values of row inserted and using this primary key of table 2 , insert values in table 4


    How to achieve this

    Any ideas

    Thanks
    DAM


    Friday, January 02, 2009 1:27 PM

Answers

  • Hi damu,
           Are you using identity column for primary key? If yes, you can get to know the primary key that is generated using Scope_Identity() function

    Example
    declare @table1id  int

    Insert into table1 values(a,b,c)

    select @table1id = Scope_identity()

    Insert into table2 values(@table1id,....)


    Jagatheesan
    • Marked as answer by Maddy06 Tuesday, January 06, 2009 12:49 PM
    Friday, January 02, 2009 1:47 PM

All replies

  • You can use the NEWID() function to set a variable and use that as the inserted value.
    If you've got several rows, you can insert several NEWID()'s into a temp-table and join with this when inserting both to the primary and foreign tables
    Friday, January 02, 2009 1:37 PM
  • Hi damu,
           Are you using identity column for primary key? If yes, you can get to know the primary key that is generated using Scope_Identity() function

    Example
    declare @table1id  int

    Insert into table1 values(a,b,c)

    select @table1id = Scope_identity()

    Insert into table2 values(@table1id,....)


    Jagatheesan
    • Marked as answer by Maddy06 Tuesday, January 06, 2009 12:49 PM
    Friday, January 02, 2009 1:47 PM
  • I prefer the OUTPUT clause of an INSERT statement over scope_identity().

    DECLARE @x table (  
       id   int identity(1,1)  
     , col1 char(1)  
    )  
     
    DECLARE @ids_we_just_created table (  
       id int 
    )  
     
    --A regular insert  
    INSERT INTO @x (col1)  
    VALUES ('A')  
     
    --One where we grab the newly inserted id(s)  
    INSERT INTO @x (col1)  
    OUTPUT inserted.id  
    INTO   @ids_we_just_created  
    VALUES ('Z')  
     
    SELECT *  
    FROM   @x  
      
    SELECT *  
    FROM   @ids_we_just_created 

    P.S. I like the suggestion from tican :)

    George
    Friday, January 02, 2009 2:04 PM