--/* setup/initialization code
CREATE TABLE dbo.SourceTableHistory (
    OldAccountNumber int NOT NULL,
    NewAccountNumber int NOT NULL,
    NewAcountNumberTime datetime NULL
    )
SET NOCOUNT ON
TRUNCATE TABLE dbo.SourceTableHistory
INSERT INTO dbo.SourceTableHistory
--add some additional history to show that it is properly handled
SELECT 066, 077, '2011-06-01 00:00:00.000' UNION ALL
SELECT 077, 088, '2011-07-01 00:00:00.000' UNION ALL
SELECT 088, 099, '2011-08-01 00:00:00.000' UNION ALL
SELECT 099, 111, '2011-09-01 00:00:00.000' UNION ALL
SELECT 111, 222, '2012-01-01 00:00:00.000' UNION ALL
SELECT 222, 333, '2012-05-06 00:00:00.000'
SET NOCOUNT OFF

CREATE TABLE dbo.AccountNumberConversions (
    ident int IDENTITY(1, 1) NOT NULL,
    RecordType char(7) NOT NULL,
    OldAccountNumber int NOT NULL,
    NewAccountNumber int NOT NULL
    )
--*/

DECLARE @rowcount int

TRUNCATE TABLE dbo.AccountNumberConversions
    
INSERT INTO dbo.AccountNumberConversions
SELECT
    'Current', OldAccountNumber, NewAccountNumber
FROM dbo.SourceTableHistory sth1
WHERE
    NOT EXISTS(
        SELECT 1
        FROM dbo.SourceTableHistory sth2
        WHERE
            sth2.OldAccountNumber = sth1.NewAccountNumber
    )

SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
    INSERT INTO dbo.AccountNumberConversions
    SELECT
        'History', sth_history.OldAccountNumber, anc_current.NewAccountNumber
    FROM dbo.SourceTableHistory sth_history
    INNER JOIN dbo.AccountNumberConversions anc_history ON
        anc_history.OldAccountNumber = sth_history.NewAccountNumber
    INNER JOIN dbo.AccountNumberConversions anc_current ON
        anc_current.NewAccountNumber = anc_history.NewAccountNumber AND
        anc_current.RecordType = 'Current'
    LEFT OUTER JOIN dbo.AccountNumberConversions anc_check_existing ON
        anc_check_existing.OldAccountNumber = sth_history.OldAccountNumber
    WHERE
        anc_check_existing.OldAccountNumber IS NULL
    SET @rowcount = @@ROWCOUNT
END --WHILE


--show list of all old account numbers and 
--the new number that should replace them
SELECT * FROM dbo.AccountNumberConversions

/*
UPDATE tt
SET
    tt.AccountNumber = anc.NewAccountNumber
FROM dbo.TargetTable tt
INNER JOIN dbo.AccountNumberConversions anc ON
    anc.OldAccountNumber = tt.AccountNumber
*/