Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Sandwiches

Sqlsandwiches is a tool for me to communicate what I have been learning to the SQL community.

Splitting ID’s with commas, updating them, and then Slamming them back together

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 with 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 then gets updated to the access DB system. We then have a job that updates the SQL DB with the Access DB. The job isn't done in SQL though. Anyhoo, the dev team was given a task to update that job. The dev lucky enough to get this task made a simple change that should have been a cakewalk.

The update when fine and dandy 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 2 other ms-access dbs. D'OH!

I was then given the task of fixing the data in the other 2 dbs asap so that 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 that there were many cases where there were more than 1 UserID in that column.
So instead of a nice USERID = 123, I had loads of USERID = 123,234. Oh man...We will now refer to this column as the AwesomeColumn.

My game plan was to take all those multiple USERIDs, split them apart, update them, and then slam them back together again. But how the hell was I supposed to do this in Access.
So I ask you to join me in my journey. Splitting ID's with commas, updating them, and then Slamming them back together.

Instead of making this a novel, I'll just get the juicy stuff. Main goal I needed to do here was update old USERIDs with new USERIDs. Some of those IDs that needed updated where in the AwesomeColumn.

First thing I did was get the latest and greatest ID and toss that dude into a temp table.


SELECT USERID, MAX(USERID) AS MAXUID INTO #max
FROM USERTABLE
WHERE USERID <> USERID
GROUP BY USERID

Next I need 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

WITH Cte AS
( SELECT
        TableID,
       CAST('' + REPLACE( USERID,  ',' , '') + '' AS XML) AS USERID
      FROM AccessDB...CommaTable    
 
)
SELECT
   TableID,
    Split.a.VALUE('.', 'VARCHAR(100)') AS UID,
    0 AS updated INTO #splitter
FROM Cte
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 like they were before. I used ANOTHER temp table to put them back to together

SELECT t.tableid,
      --Using the STUFF command
       STUFF(ISNULL((SELECT ', ' + x.USERID
                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 finally updated the real table with the updated records

UPDATE AccessDB...CommaTable  
SET USERID = u.USERID
FROM #updated u
      INNER JOIN AccessDB...CommaTable  p ON u.tableid =
p.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 different? 


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.