Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Merged/Renumbered Account Number
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

167 Posts

Posted - 03/18/2013 :  21:23:21  Show Profile  Reply with Quote
We have a system where many of our accountnumbers are getting modified. Below is a scenerio...

Source Table
AccountID AccountNumber AccountName
3 333 Test

The above table has only one record but it had two more records and they were deleted after renumbering.

Source table
OldAccountNumber NewAccountNumber NewAcountNumberTime
111 222 '2012-01-01 00:00:00.000'
222 333 '2012-05-06 00:00:00.000'

Target Table

SurrogateKey(ID) AccountID OldAccountNumber NewAccountNumber NewAcountNumberTime
1001 1 111 222 '2012-01-01 00:00:00.000'
1002 2 222 333 '2012-05-06 00:00:00.000'

AccountID AccountNumber AccountName Expired ExpireddateTime
1 111 Test 'Y' '2012-01-01 00:00:00.000'
2 222 Test 'Y' '2012-05-06 00:00:00.000'
3 333 Test 'N' NULL

AccountID for 1 and AccountID for 2 should belong to the same Account. What would be the easiest way to update AccountID of 1 and 2 to AccountID of 3? The above scenario is when the Acount Number changed once. It can actually change multiple times over a period of time.

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/19/2013 :  00:58:24  Show Profile  Reply with Quote
I see two tables with name Source. I hope thats a typo.

You could use old new values in table to get ACcountIds remapped.

SQL Server MVP

Go to Top of Page

Posting Yak Master

167 Posts

Posted - 03/19/2013 :  02:06:53  Show Profile  Reply with Quote
Its actually not a Typo.

We have 2 source tables and 2 target tables. But in the target table, there should be only one AccountID(latest one). All the old ones should be updated to the latest one. Does that makes sense?
Go to Top of Page

Aged Yak Warrior

550 Posts

Posted - 03/20/2013 :  17:44:19  Show Profile  Reply with Quote
Something along these lines should do it:

--/* setup/initialization code
CREATE TABLE dbo.SourceTableHistory (
    OldAccountNumber int NOT NULL,
    NewAccountNumber int NOT NULL,
    NewAcountNumberTime datetime NULL
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'

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
    'Current', OldAccountNumber, NewAccountNumber
FROM dbo.SourceTableHistory sth1
        SELECT 1
        FROM dbo.SourceTableHistory sth2
            sth2.OldAccountNumber = sth1.NewAccountNumber

SET @rowcount = 1
WHILE @rowcount > 0
    INSERT INTO dbo.AccountNumberConversions
        '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
        anc_check_existing.OldAccountNumber IS NULL
    SET @rowcount = @@ROWCOUNT

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

    tt.AccountNumber = anc.NewAccountNumber
FROM dbo.TargetTable tt
INNER JOIN dbo.AccountNumberConversions anc ON
    anc.OldAccountNumber = tt.AccountNumber

Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/21/2013 :  02:03:32  Show Profile  Reply with Quote
so your target will have multiple records with same latest accountno (333) ? is that your requirement?

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000