Exporting Data – The SQL Way

Pre-requisites: Microsoft SQL Server, some knowledge of SQL, and a sample database, Northwind. You can find Northwind sample databses from this webpage https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs


From time to time, you may get a request for an export of customer’s data from clients. A few things to consider when you plan to do an export.

  • What do they need the export for?
  • What file format do they need the export to be saved to?
  • What kind of data does the client need? How should the headers be ordered? Does it even need a header?
  • Do we need to remove duplicate data sets?
  • If the data would contain any dates, how does the client wants those dates formatted?
  • Check if the data is protected under GDPR. Has the customer (not client) requested for their data to not to be transferred to third parties? If they have, then their data should not be included in the data export
  • How does the client wishes to recieve the data export? FTP or by email?

If the client plans to import the data into a different system, you may need to format the export for their system, for example, date and time stamps. Their system may only accept CSV/Excel files with or without headers.

The client may want us to remove any data that are duplicates or flagged as “Do Not Transfer”. Check with the client on the above criteria.

Suppose the client needs an export from Northwind database, the export must contain a summary of order, customer, employee and shipping details. Duplicates needs to be removed from the data export. The file has to be in CSV format.

Tip: Save your SQL query to a file. You might be asked at a later date for another copy of the export with new data. Re-using this query will save you time and effort from writing up a new SQL query

SQL Query

USE [Northwind] SELECT ord.OrderID ,ord.CustomerID ,ord.EmployeeID ,ord.OrderDate ,cust.ContactName ,cust.CompanyName ,CONCAT(emp.FirstName, ' ' , emp.LastName) as EmployeeName ,ord.ShipName ,ord.ShipCity FROM Orders as ord LEFT JOIN Customers as cust ON cust.CustomerID = ord.CustomerID LEFT JOIN Employees as emp ON emp.EmployeeID = ord.EmployeeID ORDER BY ShipCity
Code language: SQL (Structured Query Language) (sql)

This is how the data export would look like in SQL

10363DRACD426/11/1996November1996Sven OttliebDrachenblut DelikatessenMargaret PeacockDrachenblut DelikatessenAachen
10391DRACD323/12/1996December1996Sven OttliebDrachenblut DelikatessenJanet LeverlingDrachenblut DelikatessenAachen
10797DRACD725/12/1997December1997Sven OttliebDrachenblut DelikatessenRobert KingDrachenblut DelikatessenAachen
Data table

Now that we have data, we can copy and paste the SQL results into a spreadsheet. Click on the top left uppermost of the results (the white corner). This will select all of the cells.

SQL result showing customer data and shipping details

Then right click and select “Copy With Headers”

It shows submenu listing options to Copy, Copy With Headers, Select All, Save results as. The option Copy with Headers is highlighted.


Create a new spreadsheet in Excel or with any other alternative application. Paste in your SQL data results. Watch out for any special characters especially any commas inside any of the text. Any commas in your data results can mess up your CSV data export by offsetting the columns.

Save your file as an Excel file for now e.g. export.xslx.

Spreadsheet showing customer data

We need to format the OrderDate. Right now, the values under OrderDate are showing as 00:00:00, no dates. Click on the column, OrderDate, to highlight all cells.

Go to the Home tab and then look for the Number format in the Number section.

Scroll down and select “Short Date”.

Your data is displaying the date format.

The next step would be to remove duplicates. Go to the tab, Data and select Remove Duplicates.

You will get a prompt asking you which columns do you want to check for any duplicates. You can tick the “My data has row headers” to see your row headers. You can select all or a few columns. Click OK to proceed.

You will get a message to confirm if any duplicates were removed. I didn’t get any this time because I had selected all columns. If you re-run the same process and then select fewer columns this time. It depends on what the client is looking for. For example, select CustomerID and EmployeeID, you will find that duplicates have been removed. In fact, half of your data may be deleted.

Removed duplicates by CustomerID and EmployeeID

Save your spreadsheet. You can export the data as a CSV file by going to File and then Export.

Choose to export as CSV (Comma Delimited) and click on Save As. Set your filename.

Double check that your data export looks fine. You can open the new file through Excel or Note, check that the columns are intact and no special characters are messing up the data integrity.

Passing on Data

If you are going to send on data to the client, zip the file with a password. The password must be relayed to the client in a separate communication. Don’t send it along with the zip file in the same communication.

SQL – Concatenate Names and get totals

In this short blog post, I’ll show you how to concatenate an employee firstname and last name, then get the total orders made under that employee.

I’m using Northwind database available from Microsoft. It’s a free sample database. You can replicate the same set of results as shown below.

USE [Northwind] SELECT emp.EmployeeID ,CONCAT(emp.FirstName, ' ' , emp.LastName) as EmployeeName ,ord.Total FROM [Employees] as emp LEFT JOIN (SELECT COUNT(*) as Total, EmployeeID FROM [Orders] GROUP BY EmployeeID) as ord ON emp.EmployeeID = ord.EmployeeID ORDER BY emp.LastName
Code language: SQL (Structured Query Language) (sql)

In the first column, it shows the EmployeeID.
In the second column, the CONCAT function wraps the firstname, a whitespace and a lastname, making up the fullname of the employee.
In the third column, it lists the orders total. This results comes from the LEFT JOIN query further in the SQL.

The LEFT JOIN shows it counts all orders and that column is named as Total, it then lists the EmployeeID. It’s grouped by EmployeeID. The LEFT JOIN query is wrapped and named as ord. It’s joined up with the EmployeeID from the Employees table.

The whole dataset is sorted by LastName. You can see the results below.

5Steven Buchanan42
8Laura Callahan104
1Nancy Davolio123
9Anne Dodsworth43
2Andrew Fuller96
7Robert King72
3Janet Leverling127
4Margaret Peacock156
6Michael Suyama67

Interviewing a Deaf/Hard of hearing candidates

Are you interviewing a deaf/hard of hearing candidate online? Deaf people can communicate by speech, sign language or both. Before the interview starts, check if they require an interpreter or captions and do they require a video call? Some deaf people rely on lip reading, so face to face interview might be needed.

A few things to know about captions. Most video call applications have auto captions but they can be hit and miss. Sometimes, captions may be mis-translated due to unclear communication or strong accents. You can enable captions on your side so you can see what’s being relayed to the candidate.

Most video call applications allows candidates to switch captions on their side. However, if you are using Zoom, the options for captions can only be enabled by the host of the video call. The candidate may not be able to understand you without captions. Please check your options in Zoom to enable captions.

How to get podcasts auto-captioned on your TV (Windows 10)


This is a guide showing you how to get podcasts auto-captioned on your TV. What you need is:

  • Your PC/laptop must have ability to screen-cast your screen to TV
  • Your TV must be capable of getting a screen cast from your PC/laptop. Alternatively, you can hook your PC to your TV via a cable.
  • Chrome Browser (latest version) installed on your PC or laptop. Preferably laptop, so you can screen cast to any TV in your area
  • At least, Windows 10 operating system


  1. Open up Chrome. Install Chrome (latest version), if you don’t have it installed. Go to the menu in your browser and look for Settings in menu

Click on Settings

2. On left hand side, click on Advanced to expand your menu options, click on Accessibility.

Click on Accessibility

3. You’ll see Accessibility options on your screen, Live Caption is one of them. Check that Live Caption is ticked. Once you’ve done that, click Caption Preferences. When you click on Caption Preferences, it will open Accessibility options on your Windows 10 operating system

Check that Live Caption option is ticked
Click on Caption Perferences

4. Closed Captions options will appear on screen. You would have the ability to change text colour, background and font size. The font size is important especially when you’re going to screencast. Whatever changes you make to your caption, you can get a preview at top of your Closed Captions settings.

I’ve set my Caption Colour as White and my caption background as black.

Set your caption preferences
Set your caption background colour.

More importantly, we need to change the Caption Size to 200%. When we screencast our screen, we can increase our Caption Size so you can read captions on screen. This also depends on your desktop screen resolution. The higher the resolution, the smaller captions may display on yoour screencast (via desktop).

Increase Captions Size to 200%.

5. In your Chrome, log into your account where you can listen to a podcast such as Spotify or Audible. For this example, I’ll be using Spotify. https://www.spotify.com/uk/

Once you’ve logged into Spotify, it will redirect you to https://open.spotify.com/ I’m playing a podcast from the BBC and my Chrome browser is adding captions on screen.

It will show captions as a narrow box at bottom but you can expand the captions to a larger box. Click on the Down arrow at middle and bottom of your captions. You can also move your captions to anywhere you like on screen, bottom, middle or top.

Captions appears as a small narrow box.
Click on Down arrow to show more lines
You can expand your captions box

6. Screen cast your desktop to your TV. This is outside scope of guide. You can use Windows to try to connect to your TV. Your TV must be capable of receiving screencasts from your PC. Alternatively, you can use a cable to hook up your PC/desktop to your TV.

One last note

Screencasting Chrome auto-captions onto your TV is no real subsitute for providing transcripts or captions for your podcasts. This is a hack, a workaround. Please use proper captioning services to caption your podcasts and all kinds of media. There is a lot of deaf people and people with non-native language would benefit from captions.

You could generate more leads and opportunities if your podcasts and videos were captioned.

Use Trello to organise your Job Hunting

Trello is a really useful tool for organising for your personal or professional projects. It can also be useful to organise your job hunting.

If you never used Trello before, you can get 10 free boards. You can use those boards for any projects you’re working on. Trello uses some Kanban concepts which allows you to monitor progress of your tasks.

The key thing about Trello is you can access your boards on any device; desktop, tablet or your mobile phone. This will help you to stay organised.

A wee sidenote. This is a guide to using Trello. You don’t have to take my guide as verbatim. Free free to customise your board to your preference.

Create an account

Create an account at https://trello.com/ and then you will see that you have Workspaces. Here’s my Workspaces set up. You can see my board for “Job Hunting”.


Create a new board “Job Hunting”

Add an alternative name if you prefer.

Create New Board prompt
Create new Board

In my Job Hunting, I created a couple of lists for my boards, so I can manage my CVs, job applications and interviews. Those lists are useful for monitoring your progress on job applications. Each item in your list is called a card. You can add lists and cards as many as you like. You can move your cards from one list to the other, as you can with a Kanban chart.

Job Hunting board
Job Hunting board

Create a “CVs” List.

You can add multiple versions of your CVs in this list, to allow you to tailor your CV to apply for a variety of roles. Add a new card in the CV list. Fill in key details such as name of CV or your skills described in CV. You can add file attachments to the card such as a word document or a PDF of your CV.

Save your CV card.

CV for Front End skills

Create a new list “Job Specs”

Add a new card, Job Specs. In this card, you should add details of the role you’re applying for.

Job Specs Card - Company name Beta Enterprises
Job Specs Card – Beta Enterprises

In the title, I had written company name, location and type of role. This will help you to quickly identify what roles you’re applying for. In the description, I’ve added contact details and the company’s website. I’ve also added job specs as a file attachment.

This card is useful in a few ways.

  1. Avoid re-applying for the same role – When you’re looking for new roles, you might be in contact with multiple recruiters offering you the same role. You don’t want to apply for the same role via different people. You can check your boards to see if you’re already in discussion with a different recruiter or that your application was turned down and you don’t want to re-apply within a short time. Some recruiters don’t like to be told by their clients that they had recieved your application from someone else. This can happen.
  2. Handy for interviews – Before you attend your interview, you can use this card to refresh your memory on the role you’re applying for. Take note of the job specs and explain how your skills/background matches the job spec. You would be able to tell the company how you are the right candidate for the role at this company.

Create a “Job Applications” list

List of job applications

List your job applications here to describe when and where did you submit the job application to. If you can, attach a copy of your job application here. You will be asked to go over your experience and background covered in your application.

You can add a deadline to remind yourself to complete that job application. You can also add labels to show progress of your job application. You could use this as an example. The colour swatch will appear above the title of the card title.

Job Applications - Progress
Job Applications – Progress

Create an “Interviews” list

You could create a single list of interview cards and set different coloured labels to denote the type of interviews, like yellow for First Stage Stage, green for Final Stage or blue for Technical Interview.

Add a new Interview card and list all important details of your interview. Maybe a date/time of your online interview with a link to a Teams call or a Google map link to their office where an onsite interview will take place.

Add plenty of notes describing nature of the interview and your selling points of your background.

Trello is an useful tool for organising progress of your job applications; from submission of application to the final stage of your interview. I recommend it.

If you have thoughts or comments, please tweet at me @jjowensdev on Twitter

Don’t Delete Records. DeleteFlag instead

If you have a process which can delete records from the database, it would be best to DeleteFlag them instead. You should add a few columns denoting when it was “deleted” and who by; DeleteFlag boolean column, DeleteUserID column and DeleteDateTime stamp.

Unless where GDPR laws applies, there are a few good reasons why you shouldn’t delete records.

Need to replicate errors

There might be something in the table record that causing errors in your main application. You might need it to replicate the issue. For example, one of the fields “DepartmentName” in the record is not being displayed on screen correctly.

If you delete this record, the problem looks as if it has gone away. It hasn’t. It will pop up again at a later time, maybe under a different record with the same error. DeleteFlag this record instead. You can test and debug the issue with this specfic record in a dev/local environment. When the issue is resolved, you can then assure the client the issue no longer exists.

Recover from accident or sabotage

If a client accidently deletes a record, we can’t recover it which will add to the client’s frustrations.

You also have to consider bad actors who will sabotage your database system via the main application, by deleting records. This happened once in my career. We had added a DeleteFlag column, which helped us to restore records immediatley.

Records needed for Auditing purposes

We may be required to retain records for auditing or legitimate reasons. In the UK, finanical services are required by law to keep financial records for 6 years from the year end.

See link for more info. https://www.accessrecordsmanagement.co.uk/guide-to-financial-records-retention/


Use a DeleteFlag to retain your records and update your main application to ignore all DeleteFlagged records when it queries for records. The DeleteFlag doesn’t have to be applied to all tables, only key tables like financial transactions.

Batch Insert Records

If you need to insert multiple records at the same time. Writing out those SQL insert queries can be laborious. Setting the same fields and then amending the values.

USE [ExampleDatabase]; INSERT INTO Employee (FirstName, LastName) VALUES ('Joe', 'Bloggs'); INSERT INTO Employee (FirstName, LastName) VALUES ('Jane', 'Bloggs'); INSERT INTO Employee (FirstName, LastName) VALUES ('Peter', 'Bloggs'); INSERT INTO Employee (FirstName, LastName) VALUES ('Mary', 'Bloggs'); INSERT INTO Employee (FirstName, LastName) VALUES ('Paul', 'Bloggs'); INSERT INTO Employee (FirstName, LastName) VALUES ('Lisa', 'Bloggs');
Code language: SQL (Structured Query Language) (sql)

You can batch insert several records with one INSERT command and add new values on each line as many you like.

USE [ExampleDatabase]; INSERT INTO Employee (FirstName, LastName) VALUES ('Joe', 'Bloggs'), ('Jane', 'Bloggs'), ('Peter', 'Bloggs'), ('Mary', 'Bloggs'), ('Paul', 'Bloggs'), ('Lisa', 'Bloggs');
Code language: SQL (Structured Query Language) (sql)

Create a Stored Procedure with a series of stored procedures

We’re going to cover on how to write a stored procedure with a series of stored procedures. That means running a number of stored procedures within one stored procedure.

You might do this for effiency and to avoid duplicating your SELECT/INSERT queries. We will create Stored Procedures to return primary keys to be subsequently used in SELECT or INSERT queries.

We’ll be using SQL Server 2017. The version of SQL Server version does not matter as long as it’s a recent version.

  1. Create or use a test database of your choice. I’m using a test database called Example Database
  2. Run the SQL query below to create new tables; Country, Town and Branch
Code language: SQL (Structured Query Language) (sql)

3. We’ll create 3 new Stored Procedures. Look up “Programmability” node in your database and right click “Stored Procedure…”. This will create a new Query window.

We’ll add a new Stored Procedure to add a new Country. Remember to change name of the database [ExampleDatabase] to your name of database.

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddCountry] @CountryName varchar(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @CountryID as int = 0; IF EXISTS(SELECT * FROM [dbo].[Country] WHERE CountryName LIKE @CountryName) BEGIN SELECT @CountryID=CountryID FROM [dbo].[Country] WHERE CountryName LIKE @CountryName END ELSE BEGIN INSERT INTO [dbo].[Country] (CountryName) VALUES (@CountryName); SELECT @CountryID = @@IDENTITY END RETURN @CountryID END
Code language: SQL (Structured Query Language) (sql)

The above Stored Procedure accepts one parameter, CountryName. It checks if the CountryName alreadys exists in the table. If it doesn’t, it creates a new Country and gets the latest primary key and stores this in the variable ID. Otherwise, it will get the Primary Key from the existing record

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddTown] @TownName varchar(MAX), @CountryID as int AS BEGIN SET NOCOUNT ON; DECLARE @TownID as int = 0; IF NOT EXISTS(SELECT * FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID) BEGIN INSERT INTO [dbo].[Town] ([TownName],[CountryID]) VALUES (@TownName, @CountryID); SELECT @TownID = @@IDENTITY END ELSE BEGIN SELECT @TownID=TownID FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID END RETURN @TownID END
Code language: SQL (Structured Query Language) (sql)

The Stored Procedure accepts two parameters, TownName and CountryID. It will check if the record already exists by querying for the TownName and CountryID. You can get same town names but in different countries. We want to avoid creating duplicate records. If a record doesn’t exist, it will create a new record with TownName and the CountryID. By adding the CountryID to the Town record, we are creating a Foreign Key where we can link Towns to Countries.

If a Town record exist, it will get the primary key, TownID, from the table Town.

One last Stored Procedure to add. In this one, we’ll create a new Branch record. This record can be a branch part of your chain of shops like coffee, clothes, or fast food.

USE [ExampleDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AddBranch] @BranchName as varchar(MAX), @TownName as varchar(MAX), @CountryName as varchar(MAX) AS BEGIN SET NOCOUNT ON; DECLARE @BranchID as int = 0; DECLARE @CountryID as int = 0; DECLARE @TownID as int = 0; -- GET COUNTRY ID EXEC @CountryID = AddCountry @CountryName; -- GET TOWN ID EXEC @TownID = AddTown @TownName, @CountryID; -- GET BRANCH ID IF EXISTS(SELECT * FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID) BEGIN SELECT @BranchID=BranchID FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID END ELSE BEGIN INSERT INTO [dbo].[Branch] (BranchName, TownID, CountryID) VALUES (@BranchName, @TownID, @CountryID); SELECT @BranchID = @@IDENTITY END END
Code language: SQL (Structured Query Language) (sql)

The above Stored Procedures has two Stored Procedures ready to be executed. Those Stored procedures will return CountryID and TownID which we can subsequently use to query for existing Branch records. If none exists, we can create a new Branch record with the branch name, Town ID and Country ID. It has logic checks added to prevent duplicate records from being built.

4. Time to test the Stored Procedure

USE [ExampleDatabase]; -- DELETE ALL RECORDS TRUNCATE TABLE Town; TRUNCATE TABLE Country; TRUNCATE TABLE Branch; exec AddBranch 'Branch Alpha', 'Glasgow', 'Scotland' exec AddBranch 'Branch Beta', 'Edinburgh', 'Scotland' exec AddBranch 'Branch Charlie', 'Manchester', 'England' exec AddBranch 'Branch Delta', 'Liverpool', 'England' exec AddBranch 'Branch Echo', 'Cardiff', 'Wales' exec AddBranch 'Branch Fox', 'Swansea', 'Wales' -- GET ALL BRANCHES SELECT b.BranchID, b.BranchName, t.TownID, t.TownName, c.CountryID, CountryName FROM Branch as b INNER JOIN Town as t ON t.TownID=b.TownID INNER JOIN Country as c ON c.CountryID=b.CountryID
Code language: SQL (Structured Query Language) (sql)

After executing the Stored Procedure to add new Branches, it will then run a query to get all branches. It will list the Branch name, Town and Country details.

In the Stored Procedure AddBranch, it will create or find Town and Country records. Then those records IDs are used to create a new Branch if it doesn’t already exists.

Deafness – Being Inclusive

As part of Deaf Awarness week, I’m writing small guides on deafness. If you have a deaf colleague, it’s important you make your deaf colleague inclusive in all things such as discussions or video calls.

Notes after a video call

After a video call, please send notes or a summary of the video call where you have discussed progress of a project or tasks for everyone to do. Most of the time, we don’t always catch everything being said. Captions don’t work 100% of the time, there might be some cross talk or we experience fatigue after spending a long time on a video call.

Your notes can cover who’s doing what, especially your deaf colleague who will know what to do

Don’t say “I’ll tell you later” and not act on it

If you’re talking to your deaf colleague and they didn’t understand what you said, please be patient and try again. Don’t say “I will tell you later” and then not act on it. Many times, we have been told “I will tell you later” and we never get a follow up. this leaves us frustrated and excluded from the conversation. By saying “I’ll tell you later”, you are proactively excluding us and insulting our intelligence thinking we will forget about it. We won’t.

We might know something which could help you or other people. We want to be part of the team

Ask us directly, not our colleagues

If you need to know something from your deaf colleague, ask them directly, not their colleagues. Like, how are they progressing with the task, what was the cause of the issue or can they give any more info. If you ask a colleague when you should ask a deaf colleague while they’re in your present company, they will feel devalued.

An example, I explained the cause of a technical issue to a colleague in simple terms (no jargon), that non-technical colleague kept looking at my peer developer for an explanation. This was my task, not my peer’s. I re-iterated the same explanation. The colleague didn’t accept my explanation, and asked my peer instead while I was sitting there. There was no problem with my speech. My peer understood me and said the same thing as I did, my non-technical colleague then accepted the explanation. This kind of thing can makes us feel devalued and not trusted.

Deafness – Video Calls

As part of Deaf Awareness Week, I have written this a wee guide if you have a deaf colleague and they are required to attend a video call such as Microsoft Teams or Zoom.

All deaf people have various levels of hearing loss and communication methods. Some can speak, use sign language or both. Something to bear in mind, deaf people might not get all of the information they need when attending a video call because of the following reasons

Auto-captions don’t exist or work 100 of the time

Captions don’t always work 100%. Some applications will auto captions, and most of the time, they are not accurate due to accents or unclear speech. Auto captions might misinterpret the speaker and this will create confusion for your deaf colleague. On some occassions, the auto-captions could stop working altogether leaving your deaf colleague in the dark.

If you’re addressing a deaf colleague, alone or part of the audience, switch on auto-captions on your side and see how it intreprets your speech. If the auto captions stops working and you see this, you can reiterate it.


Some deaf people suffer from fatigue after watching a video call for 30 mins or so. That’s because we are expending our energy on lip reading our colleagues as well as trying to keep up with auto-captions, that may not work 100% of the time. It gets tiring and hard to lip read after 30 mins. We might miss key points or instructions.

Help your deaf colleague by emailing a summary or pointers of the video call to everyone including your colleague. Please don’t single them out in the email like “Hey John, in case you didn’t catch it, here’s what to do” then CC: in everyone.

Cross talk/Unexpected Change of Subject

During a video call, there might be some cross talk which can create confusion for your deaf colleague. In some cases, the subject of discussion could abrupt change. We can lose the thread, if it’s not clear what subject we are talking now. Please make it clear if the subject has changed, we would like to give our input. If you don’t do this, we can’t give you any input and we will feel excluded from the discussion. We might know something that you don’t.