Delta change script for SQL Server

One of the things I have been working on recently (and finally cracked today) is a way of comparing two record sets and finding if anything has changed. This is handy if you want to integrate data from one system into another and need a constant feed of data. Obviously you don't want to be updating every single record every time; as soon as you get into thousands of records there's a big performance hit and it's slow.

I found this script on a website somewhere but can't remember the original source:

SELECT
refno, digit, pro_refno, par_refno,
title, forename, surname, nino, DOB, CorrelationID
FROM
tblDataOld
WHERE
CorrelationID IN(
SELECT DISTINCT
CorrelationId
FROM (
SELECT
*
FROM
tblDataOld
UNION ALL
SELECT
*
FROM
tblDataNew)
AS
xxx
GROUP BY
refno, digit, pro_refno, par_refno,
title, forename, surname, nino, DOB, correlationid
HAVING
COUNT(*) = 1)

You can see that I'm pulling data from two tables - tblDataOld and tblDataNew. The idea is that you have an out of date data set to compare against a brand new one. Replace my column names with your own and the script will show you all the rows that have a change. The correlation ID needs to be unique for each row; what I did was concatenate the first four fields together. In the dataset that I have used these are all numbers and I'm pretty certain that if you lump the four together it's unique from any other combination.

A note about the method too; originally I had three tables - Old, New and Delta. I would compare Old and New and put the changes into Delta to be processed. This does work but I found that if something happened in the job - one of the tables got truncated but not rebuilt properly - that the next time it ran it would see everything as a change and try to process all my records. As I have said above - not good. To get around this my tblDataNew is a temporary table that I'm populating for the duration of the identification process. Once I'm done I drop the table. Also, I don't use a table for the Deltas, I run the script inside an ASP.NET application and process each Delta in memory using the SqlDataReader object. I only need to store one table - tblDataOld - in my local database and I repopulate this at the end of the process so new becomes old.

It's much neater and I like the fact that it has a small footprint. If you search Google for Delta scripts you get a lot of links to applications that will identify changes for you but for something as simple as this one SQL script will suffice.

If anyone is interested in the full application let me know and I can mail you the source code; it includes all the steps for building the temporary table, processing the changes and tidying up at the end.

So you want to offer your IT skills as a volunteer?

So you want to offer your IT skills as a volunteer? Kudos to you. Here are a few things to think about. They are all based on my own experience and I hope they will help others avoid making the same mistakes I did.

1. How much time can you realistically devote?

Time is the scarcest resource of all. The internet is now chock full of open source and / or free to use software packages, programming resources and APIs. There are answers to pretty much any problem out there. Unfortunately there is only one of you, and you only have one pair of hands. It's better to invest a couple of hours a week in something that you can deliver on rather than waste a couple of hours a week on a project you won't finish because it's too big. Are your family members 'onside'? Before you tell me to mind my own business it's much easier if your family know what you are doing and are supportive.

2. What do you want to get out of volunteering?

So, what's your motivation. Volunteering shouldn't all be one way traffic - there should be something in it for you too. Have this clear from the start, and make sure the people you're volunteering with know it too. If you just want to help then great, but I saw volunteering as the chance to make my CV look more impressive than the hundred plus other people I will be competing for my next job with. One mistake I made was to start blogging about working with an organisation before I had OK'd it with them. They weren't happy at being used 'as a reference' and asked me to take all the posts down. They will tell you what their aims are - make sure you tell them what yours are too.

3. Get organised

When are you going to be able to do the work you have volunteered? If you (like me) are only going to be available in the evening and at weekends then a volunteering gig that needs you to be in someone's office at 10am once a week isn't right for you. It's also important to devote all the time you have set aside to doing what needs doing. So make sure you have all your resources (equipment, data, specifications) to hand before making a start.

4. Build a relationship

It's essential to have a good working relationship with the charity or community group that you are representing. So ditch the emailing back and forward, get on the phone, get on Skype or go and meet them face to face. Not only are you showing a more personal and professional approach you also have the opportunity to find out if you are going to be able to get along with the people you are meeting. This isn't like your day job where you're forced to interact with people you're not too keen on; it's your free time you are giving up. If your instincts tell you that it won't work then end the relationship and look for something else. And if you really like each other then you have made an important contact. To LinkedIn!

5. Keep records of what you are doing

This doesn't have to be onerous. I agreed with the charity I am working for to post text files containing updates of progress into a shared area. If you're setting up administrative passwords on applications, databases or servers don't just write them on the pad of paper next to your desk and then throw them away next time your tidy it up. Put them somewhere the organisation is going to be able to read them. This will help you in the long term - if, for some reason in the future you can't continue to volunteer then the last thing you want is being hassled for important information.

6. Enjoy it

For me, volunteering is a chance to do something I am not able to do at work. I can run my own project and make my own decisions. That for me is intellectually stimulating. Find an opportunity that is going to let you try something new, or do something a different way and use the experience for personal development. Don't do something that bores you because you won't stick at it and neither side of the volunteering arrangement profits. Have fun!

If you read this and would like any more help or advice then email me mark dot p3rry at gmail dot com.

IT4Communities

Just over a week ago I started having a conversation (via the wonders of electronic mail) with Anne Stafford. Anne is the Programme Manager for IT4Communities, an organisation that works on "Introducing Volunteer IT Professionals to Charities Needing IT Help". I have mentioned IT4Communities briefly in a recent post and was critical of the charities that advertised through it and whom I made contact with - they either ignored me or didn't follow through. The IT4Communities website and service are both excellent though - it's very easy to register and start finding people and groups that need skilled IT help.

Anne made a couple of very interesting points. The first is that there is generally a mismatch between the number of volunteers and projects for them to work on - the former is larger than the latter. I experienced this when browsing the IT4Communities database looking for volunteering opportunities. There was nothing in my geographic area (the North West of England) that I thought I could assist with and ended up writing to organisations based in Bradford and Seacroft. But this is probably to be expected. I know from working with community groups in the past that time is the most precious commodity of all and there is generally none spare to invest in training or finding someone who might be able to help them out. So engagement from volunteers is the key - I received a staggering response from one email stating that I was offering my services as a volunteer.

The second point that Anne made was about the sort of volunteer help that is out there. I have thought that a good use of my time might actually be to set up some sort of matching service that places volunteers in the Warrington area with projects that would best suit them. On this subject Anne says "companies seem keener to
respond to team challenges not skills based work". It is quite often the case that companies only have access to a limited pool of skills too and these are off limits because they're busy doing what they're employed to do. This is a shame because community groups need skills too. IT4Communities find that "SMEs are
really responsive but the larger the company the harder it is to release
staff time". As a team leader in a largish company I would agree 100% with this statement - any organisation approaching me for a 'loan' of a member of staff would be flat refused because we don't have the capacity, especially following recent budget cuts. So SMEs are the way to go - and I think the spirit of entrepreneurship and individuality that small IT companies have suit community groups best.

If you're a volunteer or a group looking for help then IT4Communities is a route you should consider following. Here's their website - and Anne is happy to answer any questions you might have.

International Thai Foundation (ITF)

I am delighted to announce the start of a working relationship with the International Thai Foundation (ITF). I was approached by John Lewis (their legal representative) shortly after I sent out my 'volunteer offering his services' mailshot back in December.

ITF is "an international not-for-profit organisation" with a clear set of objectives: "promote education, protect human rights / civil liberties and relieve poverty for the people in Thailand (including Distressed Foreign Nationals); and assist Thai people, their families and businesses integrate in England (and other countries outside of Thailand)".

I am really happy to volunteer my time to ITF, particularly in light of events such as this denial of free speech by the Thai government. My initial work has been to familiarise myself with the web hosting that ITF uses. I've installed Joomla and Wordpress onto one of the websites and can now start to get involved in the detail of what the site looks like and how we get traffic to visit it and keep coming back.

How have I ever managed without Dropbox?

The last couple of weeks have been extremely busy with a holiday and a trip to London omn behalf of my employer. Today I finally have some time to sit down and write a couple of posts...

...and also ask myself this question: 'how have I ever managed without Dropbox'? The answer is 'I'm not sure'. It's a brilliant piece of software that allows you to sync files between computers and share files with others who also use Dropbox. So I can create a Word document on this computer, stick it in my Dropbox folder and it's automatically accessible on any other computers that I am linked to. Brilliant! It looks like all the Dropbox servers do is take a backup of your material so you're still left with a copy and it gets around 'the cloud has all my data and won't give it back' issue.