I'd urge you not to write a query based only on current data, but to make it more flexible:

--
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_5\DIFF\ServerName$SQLEXPRESS_DB_4_FULL_20220327_091554.diff')


SELECT backup_path, LEFT(backup_path, LEN(backup_path) -...

It comes from the "ELSE 0", as ratbak implied.  The "0" by itself is a numeric.  Use "'0'" instead.

Btw, I think it would be better if you used a separate column for the string message to keep the diff column as only numeric, but that's just IMO.

            
April 4, 2022 at 2:22 pm
            
#4010543

            
            
                                                
        

(1) You need to verify that the 3 values are unique on at least one table; otherwise you will get the equivalent of a CROSS JOIN for all non-unique groups. ...

If you do not need full-text indexing nor filestream data on the reporting db, you could consider using a db snapshot as "DB2".  The initial create of the snapshot is...

I suggested to add them to BLOCKS...

I agree that you'd most likely be better off using a single UPDATE for specific conditions whenever possible.

However, if that's not feasible at this time, other possibilites are:

(1) Add BEGIN...

Not sure what you mean.  It did remove 'rtf' from the string.  What more, specifically, do you want it to do?

As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs...

REPLACE(Note, 'rtf', '')

If they're still too incompetent to do it, hopefully they (1) realize that and get help (2) you realize that and get rid of them if they just...

That is a classic supertype/subtyte setup.  (Similar to sys.objects being a supertype in SQL Server to subtypes of table, procedure, etc.)

But you're missing the "supertype" table.   That is, you should...

As to putting FKs on an audit table... don't EVER do that.  There's not reason to because the rows have already survived the INSERTs and UPDATEs for FKs and FKs...

Rather than just going back to the first step, I would go to a later, isolated step in the job, and ultimately that step would go back to the first...

I agree that it's real tempting to...