I GOT IT! WooHoo!
Thanks to a co-worker, this problem has been solved! I was using
scope_identity incorrectly.
This is how you should use it:
INSERT INTO Table1 (
Column1,
Column2)
VALUES (
Value1,
Value2)
INSERT INTO Table2 (
Column1,
Column2)
VALUES (
scope_identity(),
Value2)
Using scope_identity() for the value in insert #2 will return the value of
the identity field just created from insert #1.
On 2/15/07, Brad Fuller <bfuller@xxxxxxxxxxxxxxxx> wrote:
> Okay, I found the correct function and below is what I have:
>
> - Show quoted text -
> $insert1 = "INSERT INTO table1 (
<snip>
)
> VALUES (
<snip>
)
>
> SELECT scope_identity()
>
> INSERT INTO table2 (
> credit_card_id,
> case_number,
> comments)
> VALUES (
> 'scope_identity',
> '$case',
> '$comments')";
> echo "$insert1";
> mssql_query($insert1) or die ("Query failed: <br
> />".mssql_get_last_message());
>
> echo "Insert complete";
>
> the scope_identity function is suppose to select the last inserted ID
for
> the first insert statement.
>
> When my query executes, it "appears" to go thru all the steps
> correctly...it
> inserts the first record just fine, displays the echo of my query and
> returns the echo of "Insert Complete"
>
> HOWEVER, when I go to look at the data base there is NO data inserted
into
> table2 even though my query returned that "Insert Complete" statement.
>
> Any ideas?
>
>
>
> On 2/14/07, Jim Lucas <lists@xxxxxxxxx> wrote:
> >
> > Brad Fuller wrote:
> > >> >From looking on the web (MSDN) I found the @@identity and the
> > explanation
> > >> of
> > >> what it is, but MS's "example" is horrible and does not show a good
> > >> context
> > >> for using this function. Could you elaborate more on its use?
> > >
> > > $q = mssql_query("INSERT INTO TableName(...) VALUES(...) SELECT
> > > LAST_INSERT_ID=@@IDENTITY");
> > > $r = mssql_fetch_assoc($q);
> > >
> > >
> > > HTH,
> > >
> > > Brad
> > >
> >
> > Might look at this
> >
> > http://us3.php.net/manual/en/function.mssql-query.php#46026
> >
> > --
> > Enjoy,
> >
> > Jim Lucas
> >
> > Different eyes see different things. Different hearts beat on
different
> > strings. But there are times for you and me when all such things
agree.
> >
> > - Rush
I don't think you can do multiple inserts in a single query. Or maybe you
can but the select statement after it causes the second insert not to be
run? I don't know, but I've seen several examples - each use 2 separate
insert queries.
My reply to your original message was incomplete; I apologize
$q1 = mssql_query("INSERT INTO Table1 (...) VALUES (...) SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY]");
$r = mssql_fetch_assoc($q1);
$insert_id = $r['SCOPE_IDENTITY'];
$q2 = mssql_query("INSERT INTO Table2 (...) VALUES ({$insert_id}, ...)");
