PLEASE tell me I don't have to cursor thru this one by one

v2008, 'map' table has 1.7B records in it:   Aid char(16), Bid bigint

Basically, I'm using the function below converting Aid to base 36, like this:
select dbo.fn_basetodec('7A53HKUV7A79',36)
end result:  958431152002393701

I need to perform that conversion on Aid and write the end result to Bid.
example:
Aid       7A53HKUV7A79
Bid       958431152002393701

like i said, map table is pretty freeking big.  
Any insight on the most optimal means of performing this update?
CREATE FUNCTION dbo.fn_basetodec
   (@val AS VARCHAR(63), @base AS int)
  RETURNS BIGINT
AS
BEGIN
  RETURN
    (SELECT SUM(
       (CHARINDEX(
          SUBSTRING(@val, LEN(@val) - n + 1, 1),
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
       * POWER(CAST(@base AS BIGINT), n-1))
     FROM Nums
     WHERE n <= LEN(@val));
END
GO

Open in new window

LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
well,
you can just do

update table
set bid= dbo.fn_basetodec(aid)
where aid between @x and @x + 20000

and just have a counter that advances @x in 20000 in a loop
0
momi_sabagCommented:
but then again, this kind of update will generate massive amounts of log,
i would suggest you consider performing a select statement using bcp out to save the result to a file, and then use bcp in to load it into a new table

select aid, dbo.fn_basetodec(aid) as bid
from table with (nolock)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
well, i tried the select to flat file.  i got to record 95570 and failed with this:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type bigint.

surely this goes a little further than just the write to a file.  i changed @base to bigint, in the function, but it changed nothing.

do you see what may be the problem?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

momi_sabagCommented:
yes
for a very long varchar, you get a value that is greater than bigint, which is weird since bigint is very big...
try using decimal(30,0) instead

CREATE FUNCTION dbo.fn_basetodec
   (@val AS VARCHAR(63), @base AS int)
  RETURNS decimal(30,0)
AS
BEGIN
  RETURN
    (SELECT SUM(1.0*
       (CHARINDEX(
          SUBSTRING(@val, LEN(@val) - n + 1, 1),
          '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') - 1)
       * POWER(CAST(@base AS BIGINT), n-1))
     FROM Nums
     WHERE n <= LEN(@val));
END
GO
0
dbaSQLAuthor Commented:
same failure, again at 95570
i'm just running this:
select Aid,dbo.fn_basetodec(Aid,36) from dbo.tablename
0
dbaSQLAuthor Commented:
Aid max len is 16
0
momi_sabagCommented:
that is very weird,
can you isolate the problematic value of aid?
0
dbaSQLAuthor Commented:
trying to do so... but, not exactly sure how.  like i said, the table is pretty huge.  still working on it.
0
dbaSQLAuthor Commented:
ok.  i'm waaay puzzled.  all i did was add the datefield into my select:

select datefield,Aid,dbo.fn_basetodec(Aid,36) from dbo.tablename

i just wanted to be able to look at the date it was on, when it failed.  

it's still running.
i'm at 4430423

i don't want to select * this whole thing to QA.  surely the addition of the datetime field into the resultset didn't address whatever was causing the arithmetic overflow
0
Chris LuttrellSenior Database ArchitectCommented:
That could have changed the order of the values being returned so you will not run into the same record at the same record count, but it should still blow up on the same record if this is all you added.  You will have to identify the record(s) that are too large for your formula and decide what to do with them.
0
dbaSQLAuthor Commented:
>>You will have to identify the record(s) that are too large for your formula and decide what to do with them.

How?  
0
dbaSQLAuthor Commented:
well, i just attempted to write the results into a table.  you are correct, it blew up again.  of course, my insert failed.  this particular function is brand new for me.  
i'm not exactly sure how to identify the offenders
0
Chris LuttrellSenior Database ArchitectCommented:
I was trying to find what the largest int value you can store in sql and I found this
bigint MIN:-2^63 (-9,223,372,036,854,775,808) MAX:2^63 - 1 (9,223,372,036,854,775,807) SIZE:8 bytes
so if you can reverse engineer 9,223,372,036,854,775,807 through your function logic and see what string that would represent and search for a string of that length and greater than the string or of a longer length you will identify the offenders.
0
dbaSQLAuthor Commented:
sounds good.  now i just need to figure out how to implement.  i will let you know what i work out
0
dbaSQLAuthor Commented:
it's still running... lord knows how long it's going to take.  i just set arithabort and ansi_warnings off.  i am writing the Aid and Bid into a table.  if/when it completes, one assumes i will have written nulls for those which caused the overflow.  only then will i be able to figure out what is causing this, and how to address it

thank you for your input
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.