Hah, I knew it! Nice code, Drew – like you, I prefer the CTE version.

--Deleted

• This reply was modified 7 years ago by Phil Parkin.

As you've been following the forum for a long time, you'll also know that it's considered good practice to provide sample DDL, data (in the form of INSERT statements) and...

This is a technique which I have read about, but never implemented, therefore I don't feel qualified to direct you to specific links. I'm just waiting for the opportunity to...

Imagine the two sets of tables are named A and B.

It's early morning on a new day, and synonyms are currently pointing at set A, which includes the day before...

How about using Jeff's favourite method, SYNONYMs?

Have two versions of all 500 tables, which are truncated/loaded on alternate days. Use synonyms to switch between them every day. Your 'no data...

I just know that Drew is going to post a really snappy solution to this one ... that's what usually happens after I post my overblown version. But here goes...

Please provide more sample data (as INSERTs) along with desired results for that data, which demonstrates what you are trying to say.

Your code works , if you place the columns as you want to see result ( as example i provided) , but if i place all the columns in...

If you are OK with locking/blocking while the transaction completes, I suggest you look into the possibility of using

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

As long as there are always exactly two statuses, this should work:

DROP TABLE IF EXISTS #LoanStatus;

CREATE TABLE #LoanStatus
(
    LOANNUMBER INT
   ,CLOSED VARCHAR(200)
...

Nor have I been able to find a tally table solution to fixing my car's engine trouble. They're useful, but not universally so.

Can you post some sample DDL, INSERT statements...

Please provide sample DDL and INSERT statements for the above structure and you'll have a working solution very quickly.

I'd suggest that your 'real table' contains a row with a missing semicolon.

Or is this just a blog post?

Here's another way which is slightly more robust (because IsNumeric() can return unexpected results).

SELECT d.name
FROM sys.databases d
WHERE d.database_id > 4
      AND PATINDEX('%[0-9][0-9]',...