Try this:
STUFF(sourceFileName, CHARINDEX('2', sourceFileName), LEN(sourceFileName) - CHARINDEX('2', sourceFilename) + 1, '') AS FileName

Not likely to happen as long as that code is being used.   Functions in a WHERE clause make the query unable to use indexes, because it's no longer "SARGABLE".  Unless...

Here's how you might use OPENQUERY:SELECT
  --scbs.SourceID
  PL.[pkTheatreID]
  ,PL.[TheatreName]
  ,PL.[pkStartDate]
  ,PL.[pkStartTime]
  ,PL.[EndDate]
  ,PL.[EndTime]
  ,PL.[SessionStatusID]
  ,PL.[SessionStatusDescription]
  ,PL.[CaseTypeID]
  ,PL.[CaseTypeName]
  ,PL.[SessionAnaesthetistID]

Take a quick glance at your record counts.   You have to pull ALL the records from the linked server across the network in order for your local server to perform...

Think of CROSS APPLY as a cartesian product creator, and roughly equivalent to an INNER JOIN on 1 = 1 between two tables separated by commas in the FROM clause.  ...

Regression analysis is not exactly well known math.  I can't even tell you exactly what it's supposed to be able to help you figure out, or even why someone would...

First order of business.  Does that newPromoCode function need to be involved in every individual insert?  Meaning that if a record has a value of 20 for CodesOwed, then we...