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]',...

You are using command line sqlcmd, so SSMS is not relevant here.

Sure.

Can other people make changes to it?

Trying to establish whether the problem is with the table itself, or with your Access instance.

Replace

DATEPART("ww" ,GETDATE())

with

DATEPART( "ww", dateadd("d",-7,getdate() ))

I don't like this design much, but here's an idea that might help.

DROP TABLE IF EXISTS #Ops;

CREATE TABLE #Ops
(
    ProductId INT
   ,PriorityOrder...

Something like this:

INSERT INTO Archive_table (<column_list>)
  SELECT (<column_list>)
  FROM TableA AS A
    INNER JOIN TableB AS B
    ...

By 'Reports', are you referring to SSRS reports?

When you say 'few', do you mean that some reports are OK and others are not?

Do you get any errors? If so, please...

But how will you coordinate the synonym switch with what the Java app is doing?