+ Reply to Thread
Results 1 to 2 of 2

Thread: Insert Primary Key before Commit

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    31

    Insert Primary Key before Commit

    In PHP I'm trying to insert multiple rows into two separate tables , i start with :
    Code:
    mssql_query("BEGIN TRAN");
    Then I run through the insert into the main table:
    Code:
    $insert_Proposal = 'INSERT INTO PROPOSALS( ';
    
    $insert_Proposal .= 'ApprovedByDev, ';
    $insert_Proposal .= 'EstFundingEndFiscalYear, ';
    $insert_Proposal .= 'EstFundingStartDate, ';
    $insert_Proposal .= 'ProjectDesc, ';
    $insert_Proposal .= 'ProjectType, ';
    $insert_Proposal .= 'ProposalComments ';
    
    $insert_Proposal .= ') VALUES ( ';
    
    $insert_Proposal .= sqlSafeVars($_POST['ApprovedByDev'], "varchar") .', '; 
    $insert_Proposal .= sqlSafeVars($_POST['EstFundingEndFiscalYear'], "bigint") .', '; 
    $insert_Proposal .= sqlSafeVars($_POST['EstFundingStartDate'], "varchar") .', '; 
    $insert_Proposal .= sqlSafeVars($_POST['ProjectDesc'], "varchar") .', '; 
    $insert_Proposal .= sqlSafeVars($_POST['ProjectType'], "bigint") .', '; 
    $insert_Proposal .= sqlSafeVars($_POST['ProposalComments'], "varchar") .' '; 
    
    $insert_Proposal .= ')';
    What I need is to get the autonumbered ProposalID that will be created by this so I can use it to insert records into the other table, before I issue the COMMIT

    I've found suggestions to use something like:
    Code:
    output inserted.pk
    or
    Code:
    INSERT INTO Persons (FirstName) VALUES ('Joe');
    SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;
    But these don't work , could you please help me out with this ?

  2. #2
    Both solution, using OUTPUT clause or using SCOPE_IDENTITY, should work just fine even if you started a transaction. But I don't see any of them actually used in the code you posted. The OUTPUT clause must be attached to the very INSERT you write, something like:
    Code:
    $insert_Proposal = 'INSERT INTO PROPOSALS( ';
    $insert_Proposal .= 'ApprovedByDev, ';
    ...
    ...
    ...
    $insert_Proposal .= ') ';
    $insert_Proposal .= 'OUTPUT INSERTED.id '
    $insert_Proposal .= ' VALUES ( ';
    $insert_Proposal .= sqlSafeVars($_POST['ApprovedByDev'], "varchar") .', '; 
    ...
    $insert_Proposal .= ')';

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts