Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Re: Correct Perl Syntax for Last Record Insert in MS SQL?

by Perobl (Beadle)
on Nov 02, 2011 at 17:27 UTC ( #935452=note: print w/replies, xml ) Need Help??

in reply to Correct Perl Syntax for Last Record Insert in MS SQL?

SCOPE_IDENTITY() can be used this way to obtain the last record insert in MS SQL Server:

my $dbh = DBI -> connect("$dsn; server=$host; Database=$database", $us +er, $auth, \%attr) || die "database connection not made: $DBI::errstr +"; my $sth = $dbh -> prepare("INSERT INTO ocr_main (project_number, lead_ +order, employee_id, due_date, ocr_type, line_item, mech_rnr, mech_rfd +, mech_rfa, mech_dre, ctrl_rnr, ctrl_rfd, ctrl_rfa, ctrl_dre) VALUES +(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); SELECT SCOPE_IDENTITY()") + || die "unable to prepare query"; $sth -> execute($project_number, $lead_order, $employee_id, $due_date, + $ocr_type, $line_item, $mech_rnr, $mech_rfd, $mech_rfa, $mech_dre, $ +ctrl_rnr, $ctrl_rfd, $ctrl_rfa, $ctrl_dre); my $ocr_number = $sth -> fetchrow_array(); $sth -> finish(); $dbh -> disconnect();

Note I've added a SELECT along with the SCOPE_IDENTITY() function to the same INSERT string. Now simply use fetchrow_array() to retrieve the last record.

Maybe this will help someone else.

Replies are listed 'Best First'.
Re^2: Correct Perl Syntax for Last Record Insert in MS SQL?
by Anonymous Monk on Oct 16, 2015 at 20:18 UTC
    Just wanted to say thank you. Your answer was the only one that showed you could use the Select scope_identity() inside the insert query.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://935452]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (7)
As of 2018-06-24 08:59 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.