SQL Server: SELECT INTO Statement

This SQL Server tutorial explains how to use the SELECT INTO statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax

The syntax for the SELECT INTO statement in SQL Server (Transact-SQL) is:

SELECT expressions
INTO new_table
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

new_table

The new table to create with the selected expressions and their associated definitions (new_table must not exist).

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

Note

• When using the SELECT INTO statement in SQL Server, the new_table must not already exist. If it does already exist, the SELECT INTO statement will raise an error.

Example

Let's look at an example that shows how to use the SELECT INTO statement in SQL Server (Transact-SQL).

For example:

SELECT employee_id, last_name, first_name
INTO contacts
FROM employees
WHERE employee_id < 1000;

This SQL Server SELECT INTO example would select the employee_id, last_name, and first_name fields from the employees table and copy these fields along with their definitions to the new contacts table that does not yet exist.

Again, if there were records in the employees table, then the new contacts table would be populated with the records returned by the SELECT statement.

If you find that you want to rename some of the columns within the new table rather than using the original names, you can alias the column names in the SELECT INTO statement.

For example:

SELECT employee_id AS contact_id, last_name, first_name
INTO contacts
FROM employees
WHERE employee_id < 1000;

In this SELECT INTO example, we don't want the first column in the new contacts table to be called employee_id. It would be more meaningful to rename the first column in the contacts table to contact_id. This is done by aliasing the employee_id column as follows:

employee_id AS contact_id

 

SQL Server: SELECT TOP Statement

This SQL Server tutorial explains how to use the SELECT TOP statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT TOP statement is used to retrieve records from one or more tables in SQL Server and limit the number of records returned based on a fixed value or percentage.

Syntax

The syntax for the SELECT TOP statement in SQL Server (Transact-SQL) is:

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

TOP (top_value)

Returns the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If the WITH TIES clause is specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(5)
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 5 records from the employees table where the last_name is 'Anderson'. If there are other records in the employees table that have a last_name of 'Anderson', they will not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(5) WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the 5th top record is a tie, then all records in this tie position would be returned by the SELECT TOP statement. This will result in more than 5 records being returned.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is 'Anderson'. The other 90% of the result set would not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(10) PERCENT WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the last position in the SELECT TOP(10) PERCENT record set is a tie, then these tied records would be returned by the SELECT TOP statement. This will result in more than 10% of the full record set being returned.

 

SQL Server: SELECT Statement

This SQL Server tutorial explains how to use the SELECT statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT statement is used to retrieve records from one or more tables in a SQL Server database.

Syntax

In its simplest form, the syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT [ ALL | DISTINCT ]
[ TOP (top_value) [ PERCENT ] [ WITH TIES ] ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

ALL

Optional. Returns all matching rows.

DISTINCT

Optional. Removes duplicates from the result set. Learn more about the DISTINCT clause

TOP (top_value)

Optional. If specified, it will return the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

GROUP BY expressions

Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING condition

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY expression

Optional. It is used to sort the records in your result set. ASC sorts in ascending order and DESC sorts in descending order.

Example - Select all fields from one table

Let's look at how to use a SQL Server SELECT query to select all fields from a table.

SELECT *
FROM inventory
WHERE quantity > 5 
ORDER BY inventory_id ASC;

In this SQL Server SELECT statement example, we've used * to signify that we wish to select all fields from the inventory table where the quantity is greater than 5. The result set is sorted by inventory_id in ascending order.

Example - Select individual fields from one table

You can also use the SQL Server SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_id >= 555
AND inventory_type = 'Software'
ORDER BY quantity DESC, inventory_id ASC;

This SQL Server SELECT example would return only the inventory_id, inventory_type, and quantity fields from the inventory table where the inventory_id is greater than or equal to 555 and the inventory_type is 'Software'. The results are sorted by quantity in descending order and then inventory_id in ascending order.

Example - Select fields from multiple tables

You can also use the SQL Server SELECT statement to retrieve fields from multiple tables by using a join.

For example:

SELECT inventory.inventory_id, products.product_name, inventory.quantity
FROM inventory
INNER JOIN products
ON inventory.product_id = products.product_id
ORDER BY inventory_id;

This SQL Server SELECT example joins two tables together to gives us a result set that displays the inventory_id, product_name, and quantity fields where the product_id value matches in both the inventory and products table. The results are sorted by inventory_id in ascending order.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(3)
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 3 records from the inventory table where the inventory_type is 'Software'. If there are other records in the inventory table that have a inventory_type value of 'Software', they will not be returned by the SELECT statement.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the inventory table where the inventory_type is 'Software'. The other 90% of the result set would not be returned by the SELECT statement.