Using SQL in Excel

SQL in Excel Connection Setup

These steps will setup a SQL connection in Excel that can be used with multiple workbooks. The steps will also cover using this SQL connection to populate a worksheet.

  1. Open your new or existing Excel workbook
  2. Click the Data tab of the Excel ribbon
  3. Click Get External Data, select From Other Sources, and then select From SQL Server
    • This will open the Data Connection Wizard
  4. Enter the Server name
    • The server name can be found using SQL Server Management Studio
      • Open the desired connection in the Object Explorer
      • Right-click on the server and select ‘Properties’ to see the server name
  5. Enter the Log on credentials and click Next
  6. Select the Database
  7. (Option) Connect to a specific table
    • When selected, you can pick one table from the window
  8. (Option) Enable selection of multiple tables
    • When selected, check boxes will appear next to each table in the window
    • (Option) Import relationships between selected tables
      • This option allows you to use existing table relationships as opposed to building them manually
  9. Click Next
  10. Change the File Name path by clicking the Browse button
    • If your Excel file is hosted on a network share, then save the connection file to the same folder
  11. Click Finish
    • This will open the Import Data menu
  12. Select how you want to view the imported data in your workbook
  13. Select a worksheet you want to hold the data
  14. Click OK
    • The table(s) will be queried and the data populated into Excel
  15. You can update the table by clicking Refresh All on the Data tab

Modify the SQL Connection to Use a SQL Query

Using the the steps above nets you a table will all rows of data. These next steps will change the SQL query generated to one that you write.

  1. Open the Excel file
  2. Click the Data tab
  3. Click Connections
    • This will open the Workbook Connections window
  4. Select the desired connection and click the Properties button
    • If your connection does not show up, then click the Add button
    • Select the connection from the list
      • If the connection cannot be found using any of the drop-down menu options, then click the Browse button and locate the file manually
  5. The Usage tab has a couple of options regarding Refresh control
    • You can refresh the data every X minutes, when opening the file, and/or clicking Refresh All
  6. Change the selected Command Type from Table to SQL
  7. Enter the query in Command text
    • EXAMPLE: SELECT * FROM [AdventureWorks2012].[HumanResources].[Employee] (NOLOCK)
  8. Click OK
    • Saving the changes will save the connection settings to the Excel file and does not update the SQL connection file

Update Queries via Macro

After an Excel file is setup to run a query, that query can be modified via Macros. See my guide to Macros.

' Declarations
	Dim Workbook As String 'Stores the name of the workbook
	Dim connection1 As String 'Stores the name of the connection
' Initialize values
	Workbook = "SQL_Example.xlsm"
	connection1 = "ActiveEmployees"
' Update SQL Query
	With ActiveWorkbook.Connections(connection1). OLEDBConnection
	.CommandText = "SELECT * FROM [AdventureWorks2012].[HumanResources].[Employee] (NOLOCK)" & _
	“WHERE [CurrentFlag] = 1 AND [JobTitle] <> ‘Chief Executive Officer’”
	End With
	ActiveWorkbook.Connections(connection1).Refresh

Further Reading

Connect a SQL Server database to your workbook

  • Guide from Microsoft

T-SQL Primer

About T-SQL

Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.
http://msdn.microsoft.com/en-us/library/bb510741.aspx

Download T-SQL

Microsoft SQL Server 2014 Express is a free, feature-rich edition of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and for redistribution by ISVs. The SQL Server 2014 Express release includes the full version of SQL Server 2014 Management Studio instead of SQL Server 2014 Management Studio Express.
http://msdn.microsoft.com/en-us/library/dn434042.aspx

Starting T-SQL

SQL Server Management Studio is not installed by default. If Management Studio is unavailable, install it by running Setup. Management Studio is not available with SQL Server Express. Management Studio Express is available as a free download from the Microsoft Download Center, but has a different user interface than is described in this tutorial.
http://msdn.microsoft.com/en-us/library/ms166996.aspx

To open SQL Server Management Studio

    • On the Start menu, point to All Programs, point to Microsoft SQL Server 2014, and then click SQL Server Management Studio.
    • In the Connect to Server dialog box, verify the default settings, and then click Connect. To connect, the Server name box must contain the name of the computer where SQL Server is installed. If the Database Engine is a named instance, the Server name box should also contain the instance name in the format <computer_name>\<instance_name>.

Hello World!

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
[ WITH <common_table_expression>]
SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
The UNION, EXCEPT and INTERSECT operators can be used between queries to combine or compare their results into one result set.
http://technet.microsoft.com/en-us/library/ms189499.aspx

  1. Open SQL Server Management Studio
  2. Click File, point to New, and then select Query with Current Connection
  3. Type SELECT 'Hello World!'
  4. Execute the script by pressing [F5]
"Hello World!"

Variables

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.
http://msdn.microsoft.com/en-us/library/ms188927.aspx

DECLARE @message AS nvarchar(max)
SET @message = 'Hello World!'
SELECT @message
"Hello World!"

Functions

Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
http://msdn.microsoft.com/en-us/library/ms191007.aspx

BEGIN…END
Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords.
http://msdn.microsoft.com/en-us/library/ms190487.aspx

GO
Signals the end of a batch of Transact-SQL statements to the SQL Server utilities.
http://msdn.microsoft.com/en-us/library/ms188037.aspx

BEGIN
	DECLARE @message AS nvarchar(max)
	SET @message = 'Hello World!'
	SELECT @message
END
GO

Attaching a Sample Database

RELEASE NOTES
About this release
This release consolidates AdventureWorksDW databases for SQL Server 2012, 2008R2 and 2008 versions to one page. Each zip file contains an mdf database file and an ldf log file. This should make it easier to find and download AdventureWorksDW databases since all OLAP versions are on one page.
http://msftdbprodsamples.codeplex.com/releases/view/93587

An AdventureWorksDW database can be installed by attaching the database, or using the CREATE DATABASE statement.
To attach an AdventureWorks database

  1. Download a database for your SQL Server version.
  2. Unzip the database (mdf) file and log (ldf) file.
  3. From Microsoft SQL Server Management Studio, connect to a SQL Server instance.
  4. Right click Databases.
  5. Click Attach.
  6. Click the Add button.
  7. Locate the AdventureWorksDW database mdf file. For instance, AdventureWorksDW2012_Data.mdf.
  8. Click the OK button on the Locate Database Files dialog window.
  9. Click the OK button on the Attach Databases dialog window to attach the database.

SAMPLE DATABASE statement
From SQL Server Management Studio, execute the following T-SQL:
Example T-SQL

-- Select ALL records from the AdventureWorks0212 database in table Sales.Store
SELECT * FROM [AdventureWorks2012].[Sales].[Store]

Further Reading

Documentation for SQL Server 2014 Tools and Add-in Components

  • The table lists SQL Server tools and add-ins, and provides links to additional documentation in the MSDN Library.

W3Schools SQL Tutorial

  • SQL tutorial will teach you how to use SQL to access and manipulate data in: MySQL, SQL Server, Access, Oracle, Sybase, DB2, and other database systems.