Intellisense telling you it thinks there is an error isn't the same as having an error.

so do you GET an error when you execute the code?

does the index not get...

recommendation from microsoft is CU - so go that route

https://learn.microsoft.com/en-us/archive/blogs/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism

something along these lines - it assumes that none of the duplicates already contain a row that is 1 hour ahead

if object_id('tempdb..#keys') is not null
  ...

if the proc is running then using sp_whoisactive MAY give you the current explain plan - to got it the command would be

exec sp_whoisactive @get_plans = 1 -- or =...

you need to install IBM's odbc driver for I series - see https://www.ibm.com/docs/en/i/7.2?topic=oa-set-up-your-system-i-access-odbc-driver

with it you can use SSIS or C#/Powershell to connect to your as400 and then load data onto...

Ring knocker here. I have 25+ years of SQL Server experience, you should listen to what I have to say. 😉

One a personal note, continuous learner here as there...

and speaking of blocking.

whoever did the code does not really understand what "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" and "with (nolock)" do.

apart from the fact that it allows you to...

I've just looked at one of our processes - a 16 Million rows file, 5.5GB in size, 50 columns (mix of char, date, ints) - loading from a NAS share...

7 hours for 7GB... they definitely doing something wrong (like...

splitting the file should be last resource after you try out the remaining options.

• how many rows on file
• spec of server where SSIS runs
• spec of destination SQL server...

following "works" - but see comments

select t.objid
     --, t.COMMENT 
     , times.*
     , team.chg_ass_team
 ...

update will take longer but none are fast - see https://www.sqlservercentral.com/steps/stairway-to-columnstore-indexes-level-6-updating-and-deleting-data-in-a-columnstore-index

both will require index reorg to get ride of the "deleted rows" from the table.

for large etl like this one...

following up on Phil answer - go and read these 2 links.

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

https://www.sqlservercentral.com/articles/how-to-post-performance-problems-1

and as an IRL example of what Phil is asking for - you Can't build a car if the...

would be good if you could supply the full code of all required functions - as well as part of what the invocation of it is - even the invocation...

code above formatted in case anyone interested in seeing what it it

with failedjobs
as
(select serverproperty('Servername') as ServerName
      , sysjobhist.job_id
    ...