Insert into two related tables

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Brian Wolfe
    New Member
    • Feb 2011
    • 5

    #1

    Insert into two related tables

    Hey there, I'd like to insert into two tables simultaneously, where the value of the first column in the first table is the same as the value of the second column in the second table. Here's an example of what I'm trying to achieve:

    Table 1
    pri_ID, some_data
    10,words
    11,something

    Table 2
    sec_ID, pri_ID, other_data
    1,10,morewords
    2,11,somethinge lse

    Both pri_ID and sec_ID are auto increments, and some_data/more_data are inserted from php. The whole statement will be wrapped in ADOdb functions.
    Code:
    $DB->BeginTrans();
    $DB->Execute("SQL STATEMENT GOES HERE");
    $DB->CommitTrans();
    Please let me know if I need to give any more info. Thanks for your time!
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    You can't do it simultaneously. You insert into the primary key table and then insert into the foreign key table.

    Comment

    • Brian Wolfe
      New Member
      • Feb 2011
      • 5

      #3
      Just to make sure: two separate statements, with the second one calling the primary key of the first? Is there an optimal way to do that during the php function that you know of?

      Comment

      • ck9663
        Recognized Expert Specialist
        • Jun 2007
        • 2878

        #4
        Yes, two separate statements. It is recommended to do it all in sql server for you to keep the consistency of the relationship.

        Enclose your insert statements in a transaction so that if the other insert fails, you can rollback the previous one. You also need to consider to make sure your sequence of auto-number revert back to the value before the fail.

        Now, to make sure that you insert only those record that the first statement just inserted, use this clause. Either that or you create an "update-able" view to handle it.

        Happy Coding!!!

        ~~ CK

        Comment

        • Brian Wolfe
          New Member
          • Feb 2011
          • 5

          #5
          I'm not quite sure what you mean by "do it all in sql server" - I am doing it all in php, using ADOdb as my go-between.

          The statements are wrapped in a transaction - my first post makes that clear. I even gave the code of the transaction it will be wrapped in.

          So, to make this a single action, is this what you mean?

          Code:
          DECLARE @dummytablevar(pri_ID int);
          INSERT INTO table1(some_data)
          	OUTPUT INSERTED.pri_ID
          		INTO @dummytablevar
          VALUES ('somethinghere');
          INSERT table2(pri_ID,other_data)
          VALUES (INSERTED.pri_ID,'somethingelse');

          Comment

          • ck9663
            Recognized Expert Specialist
            • Jun 2007
            • 2878

            #6
            What you gave is a block of command,not transaction. I am talking about this

            Your last post tells me you're on the right track. The reason am recommending you wrap in transaction so that when there's an error on your second insert, you can rollback everything. Otherwise, you'll have data on your first table, but not on the second table. Also, identity property cannot be reused immediately. You need a couple of steps to reuse it and it has to be properly handled. Otherwise you'll have missing identity values and worse the tables being not sync.

            Good Luck!!!

            ~~ CK

            Comment

            • Brian Wolfe
              New Member
              • Feb 2011
              • 5

              #7
              Okay, I think I'm getting closer here. I'm using adoDB to talk to SQL server - here's what I have so far.

              Code:
              $sql = "INSERT INTO table1(some_data) VALUES ("somethinghere")";
              $db->StartTrans();
              $db->_Execute($sql);
              $newID = $db->Insert_ID();
              $sql2 = "INSERT INTO table2(table1_ID,other_data) VALUES ($newID,"somethingelse")
              $db->_Execute($sql2);
              $db->CompleteTrans();
              Now, from what I understand, our older version of ADOdb (3.9x) doesn't handle Insert_ID the same way as @@IDENTIFY would (it doesn't work at all during a wrapped transaction), but I'm not sure quite how to work @@IDENTIFY or some variation into the script. Any ideas here?

              Comment

              • ck9663
                Recognized Expert Specialist
                • Jun 2007
                • 2878

                #8
                Your sql variable doesn't need to have only one statement. It can contain multiple statements. As long as you split it with semi colon inside the quote. Not that semicolon outside. So your two insert statement can be in one variable as long as it can accommodate the string.

                What I am trying to recommend is to create a stored proc that you can pass those values, let your stored procedure handle the insert, the transaction processing and the @@IDENTITY. The problem with merely using @@IDENTITY is you have to consider if there are multiple session trying to update your table.

                Good Luck!!!

                ~~ CK

                Comment

                • Brian Wolfe
                  New Member
                  • Feb 2011
                  • 5

                  #9
                  The semicolon outside is the endline for php.

                  I discovered a different method that works beautifully when I put it into the SQL editor directly, but it's not functional when executing through php.

                  Code:
                  INSERT INTO table1(some_data)
                  VALUES ('some notes');
                  INSERT INTO table2(Pri_ID,other_data)
                  VALUES (IDENT_CURRENT('table1'),'something else')
                  I'll head over to the php forums for the rest of this, then. I think my issues are stemming from ADOdb at this point. Thanks for all your help, ck!

                  Comment

                  Working...