December 4, 2001 at 7:20 am
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!
December 4, 2001 at 7:46 am
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
begin
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
end
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 5, 2001 at 4:10 am
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