Actually, this is an SQL issue and not a Perl-specific one.

If you want to find the last identity after an insert and you are using MS SQL Server, you can use @@IDENTITY:

    SELECT @@IDENTITY
[download]

Unfortunately, this is not a terribly robust solution. This will return the last identity created from the current connection. However, you must run this immediately after your insert as subsequent inserts will overwrite this value. Further, if you have triggers in your database that fire when you do your insert and if these triggers do inserts, then @@IDENTITY will likely not return the data you are expecting (of course, I have serious reservations about triggers themselves). If you are using MS SQL Server 2000, this problem has been solved.

If you run the select immediately after your insert and you do not have any triggers, you should have no problem.

Cheers,
Ovid

Vote for paco!

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

@@identity also works for sybase. best used in transactions.

You will probably not be able to get the @@identity value if you use placeholders (I know this is so for Sybase, and I suspect it is for MS-SQL).

The problem is that the @@identity value is local to the currently executing code batch or procedure (which is a good thing, really), but as placeholders are implemented as temporary stored procedures the @@identity value is lost once the execution of the proc is done.

And you'd have to finish (free) the prepared statement anyway to run the query to fetch the @@identity value anyway, which would defeat (at least partially) the advantage of using prepared statements with placeholders.

Michael

SELECT max(<field>)
FROM <table>
[download]

Well, it's not incredibly easy, or I'd let you have it. ;-) The short answer is you have to do it in the other order: handle the address part first, and the customer part second.

Your flat file db repeats address information and is thus denormalized. It sounds like you've normalized the data model in SQL Server, so in addition to loading the data into the database, you need to normalize it. In this case that means generate only one row for each unique address. You can use Perl to simplify the work a little bit depending on the amount of data you're processing.

Basically, when you read a row, separate the address information and look that up in the address table first. If you get a row, remember the key value, and use that when you insert the customer data. (The Perl simplification in the case of a small data set would be to save the database key value in a hash so you don't have to keep querying.) If you don't find the address, insert it, and retrieve the new key (again storing it in the Perl hash if practical). Then proceed to handling the customer data as in the previous case.

HTH