Linked Servers

I had to import information from an Excel file with datasheets that had 40+ columns. Using SSIS could be a bit tricky sometimes, so I decided to use a linked server. This feature works well. It’s fast and less of a headache than SSIS. Originally designed for connecting to other databases, you can use it to import information by linking to a file. This is how I went about it (this is for SQL Server 2005) in importing an Excel (.xls) file.

1. Under Server Objects in your instance, create a new Linked Server:

   

2. Under the General section, pick an appropriate name for your linked server. Pick the OLE DB provider for Excel documents:

   

3. Since I’m using this on a local machine, I don’t have to worry about security too much. Select "Be made without using a security context" under the Security section.

   

4. Select your Server Options. I suggest these settings for local access.

   

5. Hit OK to create it. You’ll see the following objects:

   

SQL Server reads a spreadsheet in a workbook as a table. So now that we’ve created our linked server, let’s see how to query them.

-- Querying three spreadsheets.
SELECT * FROM Hardware...['CORE PROBONO$']
SELECT * FROM Hardware...['ET013-PartialRackElevation$']
SELECT * FROM Hardware...[ILO_TEMPLATE$]

Since I don’t always want to rely on the linked server, create tables into my general database where I slice and dice data.

-- Import data from a linked server into a database table
SELECT * 
INTO General.dbo.Elevation
FROM Hardware...['ET013-PartialRackElevation$']