DTS puzzler!

  • Anyone have any suggestions on how to do this?

    I have some data in an Excel sheet that I transfer to a table in SQL SVR 2000. There is only two fields: one for customer name and then one that holds a string of account codes separated by commas (e.g. CUS001, CUS005, CUS010).

    I want to break the account codes string up into individual codes and insert them in a new table with the accompanying customer name.

    For instance, my source data is:

    CusName AccCode

    XYZ Ltd CUS001, CUS005, CUS010

    ABC Inc CUS100, CUS003

    I want it to appear in the new table as:

    CusName AccCode

    XYZ Ltd CUS001

    XYZ Ltd CUS005

    XYZ Ltd CUS010

    ABC Inc CUS100

    ABC Inc CUS003

    I've almost broken my brain thinking about this, so some help would be really appreciated!

  • I'd import the data into a staging table having two fields. Then use a cursor which iterates through the new table and inserts each company/accountno pair into the final table. The code for the separation of accountnos is below - I've left placeholders for you to insert the cursor code:

    declare @position int

    declare @length int

    declare @AccountCode int

    declare @CustNamevarchar(500)

    declare @AccountCodes varchar(500)

    -- use a cursor to loop through each customer

    -- read the next cust name and account codes into @CustName, @AccountCodes

    set @AccountCodes = '1,2,3'

    if len(@AccountCodes) > 0

    set @AccountCodes = @AccountCodes + ','

    set @position = charindex(',',@AccountCodes)

    while @position > 0


    set @AccountCode = left(@AccountCodes, @position - 1)

    set @length = len(@AccountCodes)

    set @AccountCodes = right(@AccountCodes, @length - @position)

    set @position = charindex(',',@AccountCodes)

    -- insert into new table @CustName @AccountCode


    Paul Ibison


    Paul Ibison

  • Many thanks Paul!

    I've incorporated your ideas into a T-SQL script using a cursor etc and it works really well. I may have to revise it later to cope with extraneous spaces and non-valid values in the (unreliable) source data, but it basically works a treat. Many thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply