1. SQL Tutorial (8) SELECT INTO, CASE, NULL Functions, Stored Procedures

SQL SELECT INTO Statement

SELECT INTO: copies data from one table into a new table

• the new table is created w/ the column names and types as defined in the old table
• you can create new column names using the AS clause

Syntax

1. Copy all columns into a new table

SELECT * 
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

2. Copy only some columns into a new table

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Example (1): creates a backup copy of Customers

SELECT * INTO CustomersBackup2017
FROM Customers;

Example (2): copy the table into a new table in another database

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

Example (3): copies only a few columns into a new table

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

Example (4): copies data from more than one table into a new table

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Example (5): creates a new empty table

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

---

SQL INSERT INTO SELECT Statement

INSERT INTO SELECT statement

• copies data from one table & inserts it into another table
• requires that the data types in source and target tables match
• existing records in the target table are unaffected

Syntax

1. Copy all columns from one table to another table

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

2. Copy only some columns from one table into another table

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

---

SQL CASE Expression

CASE Expression

• goes through conditions & returns a value when the first condition is met
• works like a if-then-else statement
• if there is no ELSE part and no conditions are true, it returns NULL

Syntax

CASE
		WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Example 1: goes through conditions and returns a value when the first condition is met

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

Example 2: order the customers by City but if City is null, order by Country

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

---

SQL NULL Functions

IFNULL(): lets you return an alternative value if an expression is null

SELECT ProductName, UnitPrice * (UnitsInStock + ifNULL(UnitsOnOrder, 0))
FROM Products;

COALESCE(): does the same job as above

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

---

SQL Stored Procedures

Stored procedure: a prepared SQL code that you can save, so the code can be reused

Stored procedure syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a stored procedure

EXEC procedure_name;

Example 1: Procedure with one parameter

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectAllCustomers @City = 'London';

Example 2: Procedure with multiple parameters

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

---

This post is based on W3School's articles about SQL.