SQL SELECT INTO

What is SELECT INTO query?

SELECT INTO sql statement is used to create copy of data into another table. If you want to copy data from one table into another table, you may use this sql statement

Why DELETE query needed?

Sometime it may happen to create mirror copy of any existing table into database; you can easily create it by using SELECT INTO sql statement. This is heavily used to take back up or existing table within database or for any test purpose you may create it.


Syntax for SELECT INTO query

SELECT *
INTO NEWTABLE [IN EXTERNAL_DATABASE]
FROM EXISTING_OLD_TABLE
WHERE CONDITION;

Let’s understand SELECT INTO with different examples


Examples 1: Copy all records

SELECT * INTO Employee_New
FROM Employee;

Above sql statement will copy all records into new table called “Employee_New” from existing old table “Employee”


Examples 2: Copy all records with selected columns

SELECT Id,EmployeeCode,FirstnName,LastName INTO Employee_New
FROM Employee;

Above sql statement will copy all records into new table called “Employee_New” from existing old table “Employee” but with few columns only. It won’t copy all columns, it copy only specified columns (Id,EmployeeCode,FirstnName,LastName) into new table.


Examples 3: Copy all records with WHERE condition

SELECT Id,EmployeeCode,FirstnName,LastName INTO Employee_New
FROM Employee
WHERE Id = 1;

Above sql statement will copy all records into new table called “Employee_New” from existing old table “Employee” which has Id=1 value. It will copy only one record set into new table.


Examples 4: Copy all records with WHERE and IN condition

SELECT Id,EmployeeCode,FirstnName,LastName INTO Employee_New
FROM Employee
WHERE Id IN(1,2);


Above sql statement will copy all records into new table called “Employee_New” from existing old table “Employee” which has Id=1 and Id=2 value. It will copy only two record set into new table.


Note: You may also use different joins and multiple table to copy records from one table to another table.