Well, the /o says "the variable inside this regular expression will always have the same value". Believing that to be true, Perl will not recompile the RE. If you change the value of the variable, the RE won't change. This may be good or bad depending on what you're doing: good if you really don't change it, because you'll save time on RE recompiles; bad if you do change it, because your matches will appear to behave strangely. ("I changed the variable, why didn't it match?")

It's hard to answer your second question without a lot more knowledge about what you're doing. But my guess is that things would be a lot easier if you combine the query and reformatting into one program. The general rule: use the database for what it does best (data retrieval and manipulation) and Perl for what it does best (everything else ;-). There's no point in extracting data into a flat file and running back and forth over it: during the extract, get it in the form that makes it easiest to manipulate.

That said: if you're loading the final result into SQL Server, have you considered using DTS (built in to SS 7 and above)? There are many painful things about it (programming data transforms in VBScript -- barf), but it's hard to beat for sheer speed in server-to-server data transfers.

HTH

Actually, if you install ActivePerl, you can use PerlScript with DTS in any of the places where you normally would use VBScript. I've been using it quite successfully with DTS in SQL Server 2000, and it's an immense improvement over VBScript (obviously). It also appears to be available in SQL Server 7, although I haven't actually used it with that version.

In fact, if you execute the packages from your client machine, then I believe that ActivePerl only needs to be installed on the client, not the server. However, if you want to schedule any jobs which run on the server, then it needs to be on the server, as well.

ms sqlserver