Get inserted primary key in MS SQL before COMMIT

Question

In PHP I'm trying to insert multiple rows into two separate tables. Because these all need to succeed, I'm starting with:

mssql_query("BEGIN TRAN");

Then I run through the insert into the main table:

$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:

output inserted.pk

or

INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;

But these don't seem to work if you aren't committing the initial transaction. Thanks in advance for any help.

I think the problem is in using an autonumber; you have to use an identity in the table. See http://stackoverflow.com/questions/661998/oracle-sequence-but-then-in-ms-sql-server — Luis, Jan 18 '12 at 21:27
I'm only the web programmer on this one, no DBA priviledges, so I can make suggestions, but I have to live with the structure they provide. That said, when I said autonumber, I did mean it is defined as an identity with an Identity Increment of 1 — AlexC, Jan 18 '12 at 21:31

Garett · Answer 1 · 2012-01-19T03:07:46.670

2

You probably want to use SCOPE_IDENTITY, since it returns the last inserted identity within the current session and scope. A scope can be a batch, stored procedure, function or trigger.

edited Jan 19 '12 at 03:07

answered Jan 19 '12 at 01:56

Garett

16,632
5
55
63

Remus Rusanu · Accepted Answer · 2012-01-19T03:08:09.937

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:

$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 .= ')';

and you need to execute $insert_Proposal as a statement that returns results.

edited Jan 19 '12 at 03:08

answered Jan 19 '12 at 03:01

Remus Rusanu

288,378
40
442
569

I had originally posted an answer with SELECT IDENT_CURRENT because I thought the SCOPE_IDENTITY did not generate the value until after the commit... but researching I have since found that it generates the new ID when the insert begins whether it's rolled back or not. So, deleted my answer and voted yours up. http://stackoverflow.com/questions/5666536/sql-server-scope-identity-thread-safety – RThomas Jan 19 '12 at 03:03
Thanks Remus, I was putting the OUTPUT in the wrong place. It did indeed reserve the Primary Key even though I hadn't committed the query. – AlexC Jan 19 '12 at 15:55

Get inserted primary key in MS SQL before COMMIT

2 Answers2