Excel Worksheet as a Linked Server

As part of our testing process we periodically randomise names and details in our test database to prove that our systems aren’t dependent on a particular data set.

To achieve this I have a SQL script that randomises things like names, email addresses, phone numbers, bank details etc. To randomise the names I have an Excel spreadsheet with 1200+ first and last names and the SQL script will use these to generate completely new names.

But how to get the data into SQL from the Excel Spreadsheet? Answer Linked Servers!

The following two Microsoft links give the info:

http://support.microsoft.com/kb/321686

http://support.microsoft.com/kb/306397/EN-US/

The main bits I needed were:

Using SQL Server Management Studio or Enterprise Manager to configure an Excel data source as a linked server
SQL Server Management Studio (SQL Server 2005)
  1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
  2. Right-click Linked Servers, and then click New linked server.
  3. In the left pane, select the General page, and then follow these steps:
    1. In the first text box, type any name for the linked server.
    2. Select the Other data source option.
    3. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
    4. In the Product name box, type Excel for the name of the OLE DB data source.
    5. In the Data source box, type the full path and file name of the Excel file.
    6. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
    7. Click OK to create the new linked server.

Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains.

And then for reading in the info from the file:

The following code imports the data from the Customers worksheet on the Excel linked server “EXCELLINK” into a new SQL Server table named XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

Easy!