As the statement preceding WITH in the definition of a CTE must be terminated by a semicolon, it cannot follow EXISTS or APPLY.

I do not know the answer to your question, but I do have a couple of questions about your solution architecture.

1. Have you considered dumping all of the new/modified rows...

The usual protocol in this forum is to leave your original post intact and instead to make another post explaining what the issue was and how you resolved it. This...

Trimmed down your code a bit. Works for me.

DROP TABLE IF EXISTS dbo.CTR_PanelMembers;
DROP TABLE IF EXISTS dbo.CTRs;

CREATE TABLE dbo.CTRs
(
    CTRseqNo INTEGER IDENTITY(1, 1) NOT...

Please format your code in future.

As you have not included desired results, I have no idea what you are trying to achieve. But this query runs, at least.

            
February 10, 2022 at 11:27 am
            
#3981690

            
            
                                                
        

Single quotes are for chars. Use double quotes for string literals ... it should work.

Great. How long does it take on your live table?

Thanks for posting the link. I did not know about the 1000-row limit.

Have you tried importing a file into a table containing a column of XML datatype and then querying that? I have no idea whether it will be faster, but might...

No need for UNION:

INSERT [PW].[Contract] ([ID],[ProgramID],[PAContractNum],[PAContractAmt],[PAContractStartDate],[PAContractExpirationDate],[CreatedBy],[CreatedOn],[UpdatedBy],[UpdatedOn]) VALUES (1, 8, '15FHBFPP0115', 0.0000, '2014-07-01 00:00:00', '2016-06-30 00:00:00', 'some.person', '2015-02-11 00:00:00', 'some.person', NULL)
 ,(2, 8, '15FHBFPP0102', 0.0000, '2014-07-01 00:00:00', '2020-06-30...

If your Script Task can access the .DTSX file corresponding to 'itself', it could read the variable names from it. Not pretty and not recommended, but not impossible either.

Your existing method is probably the most practical. I considered other options and came to the same conclusions as you. I suggest that you stay well away from the XML...

It always happens that way with such "no one made any changes" such things. 😀

It would be nice if you could post the actual code you're talking about so...

Please take a look at this and see what you think

DROP TABLE IF EXISTS #LastTimeTankDrained;

CREATE TABLE #LastTimeTankDrained
(
    Tank CHAR(2)
   ,LastDrain INT
 ...

What the data represents is confusing me a little, can you expand on it a little, please?

Take TankDate 1/1/2021 as an example. There are 6 rows, with DrainSeq ranging from...