OK so this may not be possible to get exact results in T-SQL, because, as pointed out above, there is no default sort, but here is the solution I'm going with as a 'best fit' so far.
*** Double Cursor Evil RBAR monstrosity warning ***
ALTER TABLE #Table2
ADD RowNum INT;
WITH SetRows AS (
SELECT ROW_NUMBER() OVER(ORDER BY aDate) As RN, aDate, Text2
FROM #Table2 )
UPDATE T2
SET RowNum = RN
FROM SetRows AS S
JOIN #Table2 AS T2 ON S.aDate = T2.aDate AND S.TEXT2 = T2.TEXT2
--- Sod it - use a cursor
DECLARE @ROW1 INT, @ID INT, @TEXT1 VARCHAR(100);
DECLARE @MatchDate DATETIME, @TEXT2 VARCHAR(100);
DECLARE @MatchPos INT;
DECLARE @RESULTS TABLE (ID INT PRIMARY KEY, TEXT1 varchar(100), aDate DATETIME, TEXT2 varchar(100), aDate2 DATETIME );
INSERT INTO @RESULTS (ID, TEXT1)
SELECT ID, TEXT1
FROM #Table1;
DECLARE SODIT CURSOR FAST_FORWARD FOR
SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
FROM #Table1 AS T1
ORDER BY T1.ID
OPEN SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @MatchPos = MIN(T2.RowNum)
FROM #Table2 AS T2
WHERE T2.TEXT2 = @TEXT1
AND T2.RowNum > @MatchPos;
SELECT @MatchDate = T2.aDate
FROM #Table2 AS T2
WHERE T2.RowNum = @MatchPos;
UPDATE @RESULTS
SET TEXT1= @TEXT1,
aDate = @MatchDate
WHERE ID = @ID;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END
CLOSE SODIT;
DEALLOCATE SODIT;
DECLARE SODIT CURSOR FAST_FORWARD FOR
SELECT ROW_NUMBER() OVER(ORDER BY T1.ID) AS Row1, T1.ID, T1.TEXT1
FROM #Table1 AS T1
ORDER BY T1.ID
OPEN SODIT;
SET @MatchDate = '2000-01-01'; -- just a minimum date
SET @MatchPos = -1;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
WHILE(@@FETCH_STATUS = 0)
BEGIN
SELECT @MatchDate = MIN(T2.aDate)
FROM #Table2 AS T2
WHERE T2.TEXT2 = @TEXT1
AND T2.aDate >= @MatchDate
UPDATE @RESULTS
SET TEXT2 = @TEXT1,
aDate2 = @MatchDate
WHERE ID = @ID;
FETCH NEXT FROM SODIT INTO @ROW1, @ID, @TEXT1
END
CLOSE SODIT;
DEALLOCATE SODIT;
SELECT * FROM @RESULTS ORDER BY ID;
Can't wait to run it on the live tables where #table1 has 105,000 rows and Table2 has 520,000 rows!
🙁