Tuesday, August 08, 2006

Using Excel in a query

I am often asked to import data in to SQL Server. On a good day, I will be given the data in an Excel file which relates very well to a database table. On a bad day, it will come in a Word document and this will often involve a lot of copying and pasting before the data can be extracted.

On the good days, I have been using the DTS tool in Enterprise Manager to upload the data in to a table which then allows me to use it in whatever way I want. Using the OLEDB drivers allows a query running on SQL Server to join to an Excel document. Today I discovered how to use Excel as part of my T-SQL query using Distributed query.

In the example below I am using the Excel spreadsheet managers to link to my table.

I can now use the Excel spreadsheet as a normal table. A simple change to this query and I can insert the data from my spreadsheet in to a table.

Thursday, March 16, 2006

TopN Stored Procedure

One of the websites that I work on has some learning based games and the web designer would like to display the top 5 or 10 games played. A nice idea. I concluded that the number of games played that could be displayed should not be limited to 5 or 10 but could be any number, e.g. 8, 7 or 83.

I started out using the TOP keyword as part of the SELECT statement:

The problem here is that T-SQL will not allow a variable to be used within a SELECT statement. The common solution to this is to use dynamic SQL which is not pretty to look at and causes a performance hit on the server. A better solution is to use a seeded temporary table.

First create a temporary table with a seeded ID (auto identity) column along with the columns required for the result. Then insert the results from the main query into the temporary table. Remember to set the sort order you require when inserting the rows. Finally, select the results from the temporary table where the ID is not greater than the number of results you need e.g. the top 10 games

Here is my working example:

A simple solution to a common problem. Temporary tables are a very useful tool for transforming the shape data and often remove the need for dynamic SQL or cursors.