The Art of Merging

A preface, I am going to try better to document all the issues I have while programming and my solutions. I've used the wonders of other people's documentation of their solutions to problems to help me fix my own, it's time that I gave back. In reality, no one is probably ever going to come to this blog to solve a programming problem, but I gotta do my part and at least put it out there.

No, I'm not talking about merging your vehicle onto the highway, although that's another post in itself about the proper technique. I'm talking about database merging or more specifically merging data between two tables.

The Problem

I am building an interface between my home grown software on MS SQL and an out of box application that runs on an Oracle database. That isn't particularly pertinent to this solution but it's a basis of why you might want to use this technique. In this interface there is a set of Assets in Oracle and for the ease of use, all those Assets and information are copied into my program in a similarly structured table. 15-20K records are used at one time. In total, several hundred thousand.

Table Equipment in MS SQL
ID
OracleID
SerialNumber
Description
ImportantValue
Active

Table Asset in Oracle
OracleID
SerialNumber
Description
ImportantValue
Active

The problem is that once we load all the values from Asset into Equipment how do we know what changes in Asset so that we can update the records in Equipment? If I were allowed to modify the database that Asset resides in, this would be an easy run time solution using triggers. Unfortunately I am barely allowed read access. That program is locked down tight. In some cases records are added to Asset and we need to add those to Equipment as well. Records will never be deleted from Asset only deactivated.

The Solution

My first thought was to use EXCEPT to find the differences between the table and then run a CURSOR (which is just a while loop in SQL) to do an update or insert on each of the returned records. I really really didn't want to do this because a CURSOR should really be a last resort, it is slow. If you have to traverse potentially tens of thousands of records, that's not the way to go. Not to mention the code is long, and while readable isn't particularly elegant.

After some research I discovered MERGE is a newly supported function in MS SQL Server 2008, although I got the vibe it existed in 2003 as well. What happened in 2005? I don't know. All I know is I could use it in my 2008 database and that makes me happy. Now I don't claim to know everything about the execution and how all these functions work, but I do know it was fast and it did what I wanted to do and I didn't need a cursor.

Here's the full microsoft explaination of MERGE 

This page gave a sample using static values to enter or update, but I modified my own query to use another query instead.


MERGE INTO Equipment AS Target
USING (SELECT OracleID, SerialNumber, Description, ImportantValue, Active
FROM Asset)
  AS Source (NewOracleID, NewSerialNumber, NewDescription, NewImportantValue, NewActive)
ON Target.OracleID = Source.NewOracleID
WHEN MATCHED THEN
UPDATE SET
                    SerialNumber=NewSerialNumber
                    Description=NewDescirption
                    ImportantValue=NewImportantValur
                    Active=NewActive
WHEN NOT MATCHED BY TARGET THEN
INSERT (OracleID, SerialNumber, Description, ImportantValue, Active) VALUES  
                    (NewOracleID, NewSerialNumber, NewDescription, NewImportantValue, NewActive)

And there you have it, a data merge between Asset and Equipment. This case will actually select ALL the rows in Asset and compare them all and perform updates on everything it matches. If you only wanted to update or insert the differences, then you would modify the SELECT statement after the USING clause to only return the affected rows. That can be done using the EXCEPT function.

For Example


SELECT * FROM (SELECT OracleID, SerialNumber, Description, ImportantValue, Active
FROM Asset
EXCEPT
SELECT OracleID, SerialNumber, Description, ImportantValue, Active
FROM Equipment) A


This will return any records changed or added to the Asset table. I found that the query time was acceptable using the MERGE without the EXCEPT so I left it as is.

Program On.

Clean Up

When I ask Ian if he'll help me pick up toys or clean up the house:

Ian: Yeeahhhh. Cween up Cween up. *fewefs  ncjhehkan snjknads* body cween up.

He'll repeat this about 5 or 6 times or until he finds a toy while cleaning up that he wants to play with and then he's done helping. I guess they sing that song in nursery because we don't sing it here very often. It's so adorable to hear him sing it and help!

The Zone

Since I've started working again and putting in some real hours, we've been putting some upgrades into my work space. I had two 19" monitors, but one was VGA only and pretty old, the text wasn't clear and since I have issues with eye dryness anyway, it wasn't helping. We decided to get a new monitor. Somehow in all the logic and reasoning that flew around for the next few days, we wound up with a new TV and I got Brandon's old gaming monitor. And now here's my new programming environment:

If I open  Visual Studio to the full right screen I can view almost 100 lines of code. It's SO awesome. I've decided this is my most efficient set up: Running web app in the left screen, code bottom 2/3 of the right screen, SQL server in the top third of the right screen. I rarely have to do window switching now to get around everything I need in my environment. I can really whip out some code now! Also on the left, my very own work phone, love this thing. Brandon set up a VOIP server that we route through Google so I don't have to burn my cell minutes on long conference calls and the speaker phone is way better on this phone. I got everything I need, except maybe a mini fridge filled with Pepsi.

Yeah, I know you're jealous.

Peas Porridge Cold!

A couple days ago we had our first freeze. It wasn't a very hard one but it did mottle and damage my pea plants. I don't know what I'm doing wrong but these things just aren't producing for me. Maybe I'll have to try earlier next spring with a different variety. My crop last spring hardly did anything. Anyway since the plants were already a bit damaged, I decided to let Zoey go ahead and pick the peas that had developed. She gets REALLY fixated on the garden once I allow her to pick something. So every day since she asks if she can pick peas or she'll go out there and check out the plants and insist there are peas ready. I've given up on trying to restrain her since I'm pretty sure the plants won't really make much more before too many freezes start happening and kill the plants entirely. She has been quite diligent about going out and picking every single pod she can find, bringing them in and then shelling them into one of her stacking cups and asking me to keep them in the fridge so she can eat them later.  Amazingly enough she does eat them! and Ian too! I can't get them to eat peas in any other way. I guess that's another benefit of home gardening, if your kids feel like they have helped create that food, they're more willing to eat it. In total we've only had about 1/4 cup of peas, so not much to speak of but enough for a 3 year old to be thrilled.

Some Videos

Sometimes the kids will play together alone at Masten Park. When I saw this I didn't want to interrupt the fun by opening the door so I'm spying between the blinds at the back door. I dunno what all the background noise is. It's pretty hilarious watching them smack their heads numerous times and still keep playing.

Ian picks up on things a lot faster than Zoey did. Here you can see he has learned how to open a door and knows what the bathroom is for. He will frequently run to the bathroom right after he wets his diaper. We've sat  him on there a couple times but he doesn't go. He's really obsessed with the bathroom lately, but I worry  because not only does he know how to flush the toilet he realizes that it makes things disappear. He has yet to connect all those dots, but I'm sure it's only a matter of time we're fishing things out of the toilet that don't belong there.

Company Holiday Party

Brandon has worked for ExxonMobil for 4 years and this was the first year we decided to go to the Company Holiday party. I'd have to say the whole thing felt kind of awkward. When we got there, everyone was crammed into this lobby outside of the ballroom where dinner was going to be served and it was so loud you had to yell just to talk to anyone. This is also where a photographer was set up so you could get your picture taken. Each employee could have two shots so Brandon and I chose to do two poses to increase our odds of it not being a horrible picture, but we struck out. It was so homecoming feeling, and I don't know how a photographer can make a person as skinny as me have a double chin but she did it. Here is the least awkward of the two photos.

The best part of the whole night was the FOOD. It was so freakin' good!!! I'm used to eating at like 5-5:30 but dinner wasn't until 7:30, I was so hungry! It was a four corners of the world theme with South America, Europe, Asia, and North America. I went to South America first and had these AMAZING chicken empanadas with a cilantro cream sauce. If you've ever had the southwestern egg rolls at Chili's it was reminiscent of those. This section also had some really good roasted beef with a pesto type sauce. Yummy. Then I went to Europe, probably the worst selection of food but they did offer a classic greek salad that was also incredibly delicious. Brandon didn't think so, but he doesn't like feta cheese, kalamata olives or greek vinegarette. He ate before he came anyway. After that I was sooo stuffed. I really wanted to go to Asia and get some mini egg rolls but I was so full. So I then packed down a large martini glass of strawberries and whipped cream, a chocolate tulip with chocolate mousse, and a creme brule. At this point I was sooooo uncomfortable I was so full.

We chatted with some co workers and also BYU grads. Funny how we Mormons always end up gravitating to one another. We called it a night at 9:00 and went home, because we're old and have kids. I guess that doesn't explain why I stayed up til almost midnight to finish configuring a web server and install a web app. That's work for ya.

Here in My Arms

Since I graduated high school almost 10 years ago I don't play my cello very often, but every now and again (I guess about every 3 years) someone finds out that I can play and asks me to perform with them. It's always around Christmas, which is great, I love Christmas music. I'm usually really reluctant to play but eventually give in and agree because if you don't use a talent, then you lose it. While playing once every three years isn't much exercise for a talent, it's enough to keep it from completely rusting over. Every year, the Kingwood, Texas Stake of the Church of Jesus Christ of Latter-Day Saints holds a Nativity Display and Music Festival where many groups in the area perform while anyone come into the building and see hundreds of nativity scenes from all over the world. It's so beautiful to see all the renditions of this miraculous event that Christmas revolves around. In 2008 I was asked to perform this song with a vocalist, and a string ensemble including a harp. I love harp, one of these days I'm gonna learn to play one. I'll probably be really old, but I'm gonna do it! After  a couple years of chaos the vocalist contacted me and asked me to perform the song again with the group.

This is not a very well known Christmas song by Rob Gardner, or really known at all. Brandon has been on vacation all week and I've been working during the day so I was really ingrained with  programming code so I felt really off for the performance. It didn't help in my hour long cram session I gave myself a blister practicing. But I won't point out all the flaws, just enjoy the words.