Not enough details to get very specific, obviously.

However, this rules will always apply:

1. Create the clus index on all temp tables before loading them.  There are techniques for doing this...

What about opportunity cost?  The tally is used here to gen years, so the number is inherently very small and will always be so.  It is really worth the time...

But what if I need to know...

In general, agreed.  Although that specific implementation of an inline tally table is, frankly, horrible.  There's no need for anything remotely close to that number of rows (exceeding 2B for...

would be nice to have something like this i guess, sucks it doesnt work

create table #table
(old_column int)

insert into #table
values(1),(2)

alter table #table
add new_column int default (case when...

You can change the default file locations when installing SQL Server.  You can also move the system databases later if you want to.

Use ENCRYPTBYASYMKEY and DECRYPTBYASYMKEY.

No, not that way.

But, for this situation, you could make it a computed / virtual column.

ALTER TABLE [MyTable1]

ADD NewRange AS CAST(CASE WHEN Status <> 'AAA' /* OR Status IS NULL?...

Would you modify the sample data to include the "Theoretical Cycle Number", i.e. the desired cycle number you want added to each row.

I still don't fully understand all the criteria...

In your specific version of SQL, there may be different columns returned by the FILELISTONLY command.  Run a FILELISTONLY command in the main window and compare the columns that come...

If there are existing overlapping object names, I'd resolve them -- i.e. rename one of the objects -- as part of the rework.  So that, even if stored in separate...

1 Be sure to specify the schema name on the table, assuming of course that it doesn't change from user to user.

FROM dbo.MYTABLE

2. Make the variable the exact same type...

Here's the revised code.  It should work exactly as written now.

DECLARE @sql nvarchar(max)

IF OBJECT_ID('tempdb.dbo.#restore_filelistonly') IS NOT NULL
    DROP TABLE #restore_filelistonly;
CREATE TABLE #restore_filelistonly (
 ...

Something along the lines below, adjust it as needed to match your environment.  You didn't say anything about changing path names for the files, so I just used the paths...

With the sample data thing, with this one it's...