Answer moved to a different thread

• This reply was modified 5 years, 7 months ago by Jonathan AC Roberts. Reason: moved answer to https://www.sqlservercentral.com/forums/topic/substring-chrindex/#post-3721591

Have you got the recovery model set to simple?

I would put the query in a CTE and update the CTE:

;WITH CTE AS
(
    SELECT T.col3 col3,
       ...

Your data must be different from the data I provided.

If you want any further help you will have to supply some consumable data.

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

Which result? This?

Msg 321, Level 15, State 1, Line 88
"ID" is not a recognized table...

Jonathan,

You code works for hardcoded values. I replaced the Values with my #Temp1 table. I am getting an error. Any ideas?

SELECT T.Id, T.DocName, T.col1, T.col2,

T.col3 +ISNULL(LEAD(col1) OVER (PARTITION BY...

I'm just wondering why you wrote it with an inline-table?

When this would do the same:

SELECT DATEADD(day, 9 - DATEPART(dw, cdata.call_datetime), CAST(cdata.call_datetime AS date)) AS effective_date,
  ...

Hi Jonathan,

Thanks for your input but your code will not work for me. This is because the I have thousand of files that I am importing. Thus hard coded...

You could try using LEAD

SELECT T.Id, T.DocName, T.col1, T.col2, 
       T.col3 +ISNULL(LEAD(col1) OVER (PARTITION BY T.DocName ORDER BY T.Id),'') col3
...

Can you run the tests on the indexes in a different environment, like UAT or system test?

Can you paste in what indexes are on the table and which ones you...

hey,

hold on - wasn't this why hierarchyID types were invented ?

you can even use persisted computed columns to make it nice and fast.

you would need a 1 time hit...

It's a good idea from Anthony, you might get even better performance if you also add an index to #TEMP:

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
   ...

• This reply was modified 5 years, 7 months ago by Jonathan AC Roberts. Reason: maybe a different index

I think performance might improve if you add an index on SourceId including other columns in the query.

Try this and see if there is an improvement:

 CREATE...

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from...

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from #holders h
...