Splitting IDs with commas, updating them, then slamming them back together!

commensally Unfortunately, my company still uses MS Access DBs. That’s Access 97, folks! I’ve battled many challenges over the last couple months with these databases. It hasn’t been fun, but it’s been a great learning experience.

I had a fun adventure last week dealing with one of these DBs. We have a user access DB that connects to our SQL user DB. If HR updates something in their system, it also gets updated to the Access DB system. Then we have a job that updates the SQL DB with the Access DB. The job isn’t done in SQL, though. The dev team was given a task to update that job. The dev team that was lucky enough to get this task made a simple change that should have been a cakewalk.

The update was completed easily, but no one took consideration of the dependences. We didn’t even know we had dependences on this old job/db until the emails started rolling in.

Long story short, we found the problem and were able to update it. The bad part was fixing the busted data. We found out that this DB was tied to two other MS-Access DBs. D’OH!

From there I was given the task of fixing the data in the other two DBs a.s.a.p so people could work on their documents. I thought, “No big deal, update some old Access DBs”. Then I saw the data in the DBs.

The field I had to update was tied to the UserID in the User DB. This wouldn’t have been a problem except there were many cases where there was more than one UserID in that column.

Instead of a nice USERID = 123, I had loads of USERID = 123,234.

My game plan was to take all those multiple USERIDs, split them apart, update them, then slam them back together again. How the hell was I supposed to do this in Access?

Instead of making this a novel, I’ll just get to the juicy stuff. The main goal was to update old USERIDs with new USERIDs.

The first thing I did was acquire the latest and greatest ID and toss that dude into a temp table.


Next I needed to get all the columns that had commas, split them up, and put them into a temp table. For this I used a snippet I found here that used CTE and XML

       CAST('' + REPLACE( USERID,  ',' , '') + '' AS XML) AS USERID
      FROM AccessDB...CommaTable    
    Split.a.value('.', 'VARCHAR(100)') AS UID,
    0 AS updated INTO #splitter
CROSS APPLY UID.nodes('/M') Split(a)

Next, I updated the temp table(#splitter) with the newest IDs

UPDATE  #splitter
SET UserID = m.maxuid,
updated = 1
FROM #splitter s INNER JOIN #newUID m ON s.UID = m.uid
WHERE tableid IN (
SELECT tableid
FROM #splitter
GROUP BY tableid
HAVING COUNT(tableid) > 1)

Finally, I had to smash those guys back together as they were before. I used another temp table to put them back to together

SELECT t.tableid,
      --Using the STUFF command
                FROM #splitter x
               WHERE x.tableid = t.tableid
            GROUP BY x.USERID
            --AND using XML
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') AS USERID INTO #updated
  FROM #splitter t
  WHERE updated = 1
GROUP BY tableid

And then I  updated the real table with the updated records

UPDATE AccessDB...CommaTable
FROM #updated u
      INNER JOIN AccessDB...CommaTable  p ON u.tableid =

It was a nice adventure and hopefully it’s something I can use again.
Have you been in this situation before? What did you do? What would you do differently?

So much Internet…


When it comes to the SQLs, I know only a fragment of what I could. To fill in those gaps, I read. Oh, do I read. I’ve only read a few SQL books but I have read hundreds of articles and blogs.

Most of the time it goes like this:

“Hmmmm, that looks like an interesting article. Let me give it a nice ‘ol read”

“Oh…what’s this link? To another blog? Sure! Why not?!”

time to reeeeaadddd

Then I must pull myself away and jump back into the databases.

I like blogs that stick to material I am looking for while also keeping my attention. At the same time, I try to avoid blogs that get too personal or complain too much. I keep my personal blog personal and my technical blog focused on the SQLs. I like to read blogs that try to do the same.

Here is my list of the Top 10 blogs I prefer (in no order):

  1. Grumpy Old DBA – the name says it all
  2. DBA Rant – good stories here
  3. SqlBalls
  4. SQL Rockstar
  5. Shaun J Stuart – my recent favorite. Not a ton of updates but great posts
  6. Pinal Dave – Used to be my favorite but there is such a thing as too much content – it’s hard to keep up
  7. Happy SysAdmin – Great for learning some Admin skillz
  8. Paul White – Wowzer. This guys knows a lot.
  9. sqlchicken
  10. SQL Princess – She loves some CTE’s
I like bloggers who I can relate to. Many times they have the same problems I have and/or present alternative solutions.

The last thing I would like to say to all these blogs is – thanks! You’ve helped me a lot.

Do you read blogs? If so, which ones?