Off the top of my head (like the rest of my hair), it looks like you cannot count on 'len (last_name) - 9)' being greater than zero for every value...

Make stored procedures out of at least one of them, and use the EXEC verb to execute it.

Hmmm.  That's quite a list of constraints.  Can't lock out other users.  Can't use a cursor.  Can't add a trigger.

Well, I'll take another stab.  This goes row by row, but...

Something else to consider is using a trigger on 'myTable' to isolate the new identity values, all of which will be contained in the 'inserted' virtual table.  I should have...

Hmmm.  It might not work as coded, but not for the reasons you suggest, or so I think.

The BEGIN TRANSACTION locks out other users while you are inserting, so once...

You might have to try something like this, but I hope there's an easier way:

CREATE PROCEDURE dbo.myProc ( @id INT)

AS

  CREATE TABLE #holdtable

    (cntr INT NOT NULL PRIMARY...

Off the top of my head, I'm thinking NULL should have worked if the accepting table column allows NULL, and in any event if it didn't, that's a whole nother...

Can you show some of the data?

I think I could have given you a better query earlier.  Try:

SELECT * FROM tableA WHERE ISNULL (FieldNameA, '') = '' OR ISNULL...

Interesting problem, I plan to stick around until someone answers it.  My gut response would have been to say, impossible, that can't happen.  On second thought, however, I'm wondering if...

I'm not a lumberjack, but I'm still okay.

At the moment, I would doubt that all the data coming in adheres to your specified format.  Run something like the following:

SELECT * FROM tableA WHERE ISNULL (FieldNameA,...

For me, nothing ever topped, "How not to be seen". 

Well, my wife just *might* challenge me to a math duel at some point.  I majored in math, and therefore I can't do simple arithmetic.  Also, I don't fit the...

That's because women are verbally more astute than men.

When was the last time your wife challenged you to a math contest?

...wouldn't be prudent.