Given the ID 3347 value, can you confirm that the grouping should be on Name only?

And how do you determine the order in which the new IDAII values are assigned?

The only reason I can think of would be if there is other code which pertains only to the first condition – like this

IF @Var1 = @VarA
...

Try this instead

SELECT TOTAL_ACCT_NUM = COUNT(ACCT_ID) OVER (PARTITION BY ACCT_ID)
      ,ACCT_ID
      ,BUSINESS_NAME
     ...

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, 4 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...