Well, if we don't know what your real data looks like, it's difficult for us to help.  That's why I asked for representative sample data along with expected results.

John

What indexes do you have on the tables?  Please post an actual execution plan for the query (the .sqlplan file, not just a picture, please).

John

So your actual code really really uses only one line of data?  If that really is the case, why do you need MAX, DISTINCT and PARTITION BY?

John

Looks like your browser or something else has changed the less than sign into the HTML code "<".

John

You're not selecting from any tables, so there's nothing to update.  Just include that particular manipulation in your SELECT statement.  Your actual data clearly has more than the one row...

There's probably further improvements you could make, but here's how to eliminate the temp table:

WITH y AS (
    SELECT 
         testing1
    , ...

I think you'll need to reconfigure your linked server object as well.  The error message suggests that you are trying to force Windows credentials where it's expecting a SQL login. ...

Without any sample data, or knowing what is garbage about your results, it's difficult to help you.

Presumably you're looking for the top 5% of durations for each proc?  In which...

• This reply was modified 6 years, 5 months ago by John Mitchell-245523.

Simon

Have you tried the NTILE function?

John

I agree with Y.B. - expected results would make it much clearer what you're after.  For example, what do you mean by "deleted or updated"?

Here's a guess.  It doesn't fit...

• This reply was modified 6 years, 5 months ago by John Mitchell-245523. Reason: Corrected typo

It's because you haven't set a value for @Processed.  Also, this is going to break if you ever update more than one row with a single UPDATE statement.  Do it...

Are you using SQL Server?  There's no such thing as "Now()", as far as I know.

Something like this may work for you:

UPDATE B
SET expired = -1
FROM GCDF_DB..BillingThirdNotice...

Yes, aklt's solution is right.  But be careful in case any rows were legitimately deleted after the accidental deletions - you won't want to include those in your INSERT statement.

John

I'm not sure that a relational database is the place to store unstructured data like this.  The reason I say that is that you don't know what data is going...

I'd be tempted to import it into a single-column staging table and do the transformations from there.

John