Stepping back a little, have you tried importing your database into a Visual Studio database project and then attempting a build? That should throw out various errors and warnings to...

What do you mean by XML query? An XPATH query?

Can you please provide sample DDL and INSERT scripts to create your sample data?

Can you also describe exactly how the dataset should be ordered?

Once you have some ordering in place, one solution is as follows:

DROP TABLE IF EXISTS #SomeTab;

CREATE TABLE #SomeTab
(
    rn INT NOT NULL
  ...

• This reply was modified 5 years, 7 months ago by Phil Parkin. Reason: Fixed slight logic error in query

This is not possible as it stands, because the NULL rows do not have any ordering.

What I mean is, there is nothing to associate the first two NULL rows with...

The fact that the load to text file is quick suggests (to me, at least) that one or both of

• Bandwidth between SQL Server database and RDS
• The Aurora database

is...

Try something like this (I wasn't sure of the names of your source and target tables, so I'll leave that to you):

DECLARE @MaxInsertDate DATETIME
   ...

Could you

SELECT MAX(Last_Updated)

from your target table and use that instead?

+1, if you don't know when it last ran, this is close to...

When the job runs, does it write to a log table? (Containing, for example,...

You need to set the value of the @LastJobRun parameter before executing that query.

Do you have this stored somewhere?

You could automate the backup/restore process with a bit of scripting.

If you have enough space on your 'data query' server to host two versions of the database, you could do...

You could create a calculated, persisted column in your table definition and SELECT that instead.

And mine didn't? For the sample data you provided, I think it did; it's fast too.

If, as Scott no doubt correctly surmised, your actual data contains variations on...

Really? >8,000 points and you still cross-post!

Answers here, please.