To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next)
one of the procedures that I'm trying to write is to be able to take parameters such as an action (add, delete, modify), a source(which is a query), a target (which is a table) and keys (which will be used to join the source and target to do the action)... and this for any source to any table within our application... This being said, I have been able to create the procedure. Everything worked fine until we hit a big source and target (15 million rows).
one update statement was too big and an update statement for every row was too long... So I've managed to get the update to work in chunks of 500K rows in a test environment which performs way better but in my test lab I knew the source and the target so I was using a specific field from the source and Target to make sure I take the next 500K rows.
pseudo-code of my tests look like this.
DECLARE @BatchSize INT = 500000, @ROWCNT INT, @ROWTOTAL INT
WHILE 1 = 1
BEGIN
UPDATE TOP (@BatchSize) PRI
SET {List of columns to update}
FROM ({Source}) BAT
JOIN {Target} PRI ON {List of Keys}
WHERE PRI.F1 <> BAT.F1 -- F1 is updated by the list of columns to update
IF @@ROWCOUNT < @BatchSize BREAK
END
The problem I'm having is that I want to use similar code but since I don't know what fields exist in the list, I can't use the WHERE PRI.F1 <> BAT.F1 which means that My loop would always pickup the same 500K rows (top 500K).
To counter that problem, I would need to use the ROW_NUMBER function but in my eyes it would double the execution time since I would have to create a temporary table that includes the row_number value and logic that will take the next 500k and so on.
Can anyone think of any other way (using physical addresses or something else) to be able to pick up the next 500K rows without having to create a temp table?
your're input is greatly appreciated. Thank you JG