You can use the PatIndex if you want... I prefer the regular expressions because they seem to be simpler and they run with good speed...

For example, if you had...

Dang... fat fingered that one   Meant to say that the deleted table is empty for INSERTs... thanks for the catch, David.

First... replace all the CHAR(13) occurances with ';' including the single quotes.  Some use similar code to make a function where they can pass the delimiter to the function but...

Maybe that's the difference... I'm running SQL Sever 2000 Enterprise Edition with SP4.  Just kidding... I get the same execution plans on an SP3a box as well.  I don't know...

My managers at work liked it so well, they allowed the term in our coding standards... and, ALL the developers know what RBAR means... no need for a lengthy explanation...

ISNUMERIC does NOT check for numeric characters only... be careful.

Your code to handle the header appears to have a couple of a problems... as you are aware, the Deleted table is empty for Deletes... why does the SELECT in the...

>>Then generate the header record and pick up its unique id – (how do I get this).

Right after you insert into the header table, do a SELECT @variable = SCOPE_IDENTITY()...

Aren't you missing a "TableName" or TableID column?  

I ran your code against the Northwind database (assume that's where you were running it, as well)... Just to be sure, this is your code, isn't it (I added the...

City Index?  Neither the code you posted or my code would even come near a "city" index if the Primary Key was the CustomerID column.

The thing I'd like to add to Steve's good comments is that you should not use SELECT * It's, well... a bit lazy... and...

Actually, you can get ALL the column names at once with the commas at the end with the added benefit of carriage returns in just the right places... run this...

There's that "ISNUMERIC" thing again... see a couple of frames up for the warning