SQL SELECT INTO

by

The SQL SELECT INTO statement has different implementations from one product to another. This topic will talk about its implementations in MS SQL Server, Oracle and MySql.

MS SQL Server

For MS SQL Server, the SQL SELECT INTO statement is used to select data from one or more table and insert it into a different table. It’s useful for table’s backup.

SQL SELECT INTO Syntax

Select all columns into a new table:

SELECT *
INTO table2
FROM table1

Or list the columns’ name you want to insert into the new table:

SELECT column1, column2, column3, ...
INTO table2
FROM table1

SQL SELECT INTO Example

Make a copy of all the data in “Employees” table into “Employees_Bak” table:

SELECT *
INTO Employees_Bak
FROM Employee

Only copy some columns into the new table:

SELECT EmployeeId, FirstName, LastName, Title
INTO Employees_Bak
FROM Employee

Use the IN clause to insert the copy to another database:

SELECT *
INTO Employees_Bak IN 'Bak.mdb'
FROM Employee

Use the WHERE clause only copy the new employees:

SELECT *
INTO Employees_Bak
FROM Employee
WHERE StartDate >=  '2010-06-01'

Select data from multipule tables:

SELECT Employees.EmployeeId, Employees.FirstName, Employees.LastName,Projects.ProjectId
INTO Employees_Project_Bak
FROM Employees
INNER JOIN Projects
ON Employees.EmployeeId = Projects.EmployeeId

Oracle PL/SQL

For Oracle, the SELECT INTO clause is used to retrieve one row or set of columns from one or more database tables, and assigns the selected values to variables or collections.

Oracle PL/SQL SELECT INTO Clause Example

Select a employee’s last name into a variable:

DECLARE
    v_EmployeeName Employees.LastName%TYPE;
BEGIN
    SELECT LastName 
    INTO v_EmployeeName 
    FROM Employees      
    WHERE  Employees.EmployeeId = 203;
    dbms_output.put_line('Name: '||v_EmployeeName);
END;
 /

Select all the columns to a collection:

DECLARE
    v_Employee Employees%ROWTYPE;
BEGIN
    SELECT * 
    INTO v_Employee 
    FROM Employees      
    WHERE  Employees.EmployeeId = 203;
    dbms_output.put_line('Name: ' || v_Employee.FirstName || '  ' || v_Employee.LastName);
END;
 /

Select First Name, Last Name into one variable:

DECLARE
    v_EmployeeFullName VARCHAR2(100);
BEGIN
    SELECT FirstName || ' ' || LastName
    INTO v_EmployeeFullName
    FROM Employees      
    WHERE  Employees.EmployeeId = 203;    
END;
 /

Select First Name, Last Name into two variables:

DECLARE
    v_EmployeeFirstName Employees.FirstName%TYPE;
    v_EmployeeLastName Employees.LastName%TYPE;
BEGIN
    SELECT FirstName, LastName
    INTO v_EmployeeFirstName, v_EmployeeLastName
    FROM Employees      
    WHERE  Employees.EmployeeId = 203;    
END;
 /

The SQL SELECT INTO statement can only retrieve one row of data. If you want to retrieve more than one row of data, you could use the SQL SELECT BULK INTO statement:

DECLARE
    TYPE Employees_Table IS TABLE OF Employees%ROWTYPE INDEX BY PLS_INTEGER;
    v_All_Employees  Employees_Table;
BEGIN
    SELECT *
    BULK INTO v_All_Employees
    FROM Employees;
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);   
END;
 /
MySQL

For MySQL, the SQL SELECT INTO is used to retrieve data from one or more database tables to variables or use SELECT INTO OUTFILE statement to write the results of a SELECT statement into a text file on the server.

Select First Name, Last Name into two variables:

DECLARE v_EmployeeFirstName  VARCHAR(30);
DECLARE v_EmployeeLastName  VARCHAR(30);

SELECT FirstName, LastName
INTO v_EmployeeFirstName, v_EmployeeLastName
FROM Employees
WHERE EmployeeId = 203;

Use the SQL SELECT INTO OUTFILE statement to write the results into the file /download/employees.txt:

SELECT EmployeeFirstName, EmployeeLastName, Department, Salary
FROM Employees
INTO OUTFILE '/download/employees.txt'

We can also put some format informations:

SELECT EmployeeFirstName, EmployeeLastName, Department, Salary
FROM Employees
INTO OUTFILE '/download/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

In the above example, each field will be enclosed in double quotes (“), seperated by commas, and each row will be seperated by a new line (\n). The output would look like:


"Mary","Fox","Finance","78000"
"Joe","Lanyon","Finance","45800"
"Sally","Daff","Finance","57000"
...

Leave a Comment