Useful Things to Do with SELECT

The T-SQL SELECT statement is the primary tool for retrieving data from SQL Server, but SELECT also has several other interesting uses. Here are seven useful things that you can do with SELECT besides returning rowsets to your application.

7. Set the Content of a Variable

One of the most basic things you can do with a SELECT statement is to assign values to a variable. Use the following code snippet to assign the string 'New Value' to the variable named @str:

DECLARE @str varchar(30)SELECT @str='New Value'

6. Retrieve the Current Database Name

SELECT can also retrieve system information. The following SELECT statement uses the db_name() function to return the current database name:

SELECT db_name()

5. Limit the Number of Returned Rows

With SQL Server 7.0, Microsoft introduced the TOP keyword, which you can use to limit the number of rows that a query returns. The following example uses TOP to select just the first five rows from the authors table:

SELECT TOP 5 * FROM authors

4. Create a New Table

SELECT can create a new table based on a query's contents. Use the INTO keyword to create a new table named authors2 that consists of the same rows as the authors table:

SELECT * INTO authors2 FROM authors

3. Remove Duplicate Rows from a Table

The DISTINCT keyword lets you extract just the unique rows from a table and write them into a new temporary table:

SELECT DISTINCT * INTO #temptable FROM badtable

2. Modify Your Results with the CASE Statement

The CASE statement lets you modify the results that a SELECT statement returns by evaluating column values and substituting new values. The following code shows how to replace the state code CA with the state name California:

SELECT au_id, au_lname,  CASE state      WHEN 'CA' THEN 'California'  END AS StateFROM authors WHERE state = 'CA'

1. Select from an Excel Spreadsheet

You can use the OpenRowset() function with the SELECT statement to directly select the contents of a Microsoft Excel spreadsheet. The following example shows how to select the contents of the worksheet named data in the spreadsheet named MyWS in the C:temp folder:

SELECT * FROM OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:tempMyWS','SELECT * FROM [data$]')