How to Backup Table Using SELECT INTO Statement

SELECT INTO Statement is used to select data from an existing table and insert into a new table. It makes the new table similar to existing table with all columns and types and then insert data into new table. This is extremely useful when you want to make a copy/backup of an existing table.

The Syntax for SELECT INTO Statement can be as shown below:

SELECT * INTO BackupTable FROM ExistingTable

For Demo, let’s create two tables, tblDepartment and tblEmployee first.

CREATE TABLE tblDepartment
 (
    DeptID INT PRIMARY KEY NOT NULL,
    DeptName NVARCHAR(50) NULL
 )
 GO
 
CREATE TABLE tblEmployee
 (
     EmpID INT IDENTITY(1,1) PRIMARY KEY,
     EmpFirstName NVARCHAR(20),
     EmpLastName NVARCHAR(20),
     EmpAge INT,
     EmpGender NVARCHAR(10),
     EmpEmail NVARCHAR(50),
     EmpPhone NVARCHAR(20),
     EmpDeptID INT FOREIGN KEY REFERENCES tblDepartment(DeptID),
     EmpSalary INT,
     EmpJoiningDate DATETIME,
     EmpCountry NVARCHAR(20)
 )
 GO

Let’s insert some records in tblDepartment and tblEmployee tables.

INSERT INTO tblDepartment
 VALUES
     (1,'HR'),
     (2,'Finance'),
     (3,'Marketing'),
     (4,'Sales')
 GO

 INSERT INTO tblEmployee
 (
     EmpFirstName, EmpLastName, EmpAge, EmpGender, EmpEmail, EmpPhone, EmpDeptID, EmpSalary, EmpJoiningDate, EmpCountry 
 )
 VALUES
     ('john','dave',26,'male','john@gmail.com','0923044554561',1,26000,'2010-02-01','USA'),
     ('kamran','akmal',35,'male','kami@gmail.com','0923044545562',2,25000,'2011-01-01','Pakistan'),
     ('umar','akmal',36,'male','umar@gmail.com','0923044554563',3,38000,'2015-01-01','Pakistan'),
     ('hafeez','sheikh',25,'male','hafeez@gmail.com','0923044554564',2,25000,'2016-05-05','India'),
     ('amar','sidhu',36,'male','hafeez@gmail.com','0923044554560',2,25000,'2016-05-05','India'),
     ('simran','sidhu',25,'female','simran@gmail.com','0923044554565',2,33000,'2018-01-01','India'),
     ('diyana','jeni',26,'female','diyana@gmail.com','0923044554566',3,50000,'2005-01-01','UK'),
     ('maria','anders',27,'female','maria@gmail.com','0923044554567',1,70000,'2004-01-01','UK'),
     ('anam','chaudhary',29,'female','mahjabeen@gmail.com','0923044554568',1,25000,'2020-01-01','Pakistan'),
     ('Amelia','sidhu',56,'female','amelia@gmail.com','0923044554569',2,50000,'2015-01-01','USA')
 GO

1. Copy all rows to new table from existing table.

First, we move all rows from an existing table tblEmployee into a new table tblEmployeeBackup. The following query will generate the same backup table with all columns and types and then transfer all the rows into tblEmployeeBackup table.

SELECT * INTO tblEmployeeBackup FROM tblEmployee

we can see the result of above query in the following image.

2. Copy only selected columns into a new table.

We can copy selected columns with data into a new table by using SELECT * INTO Statement. For example, if we want to copy only EmpID, EmpFirstName, EmpLastName, EmpGender and EmpPhone from the tblEmployee table into a new table tblEmployeeBackup then we write the query as.

SELECT
EmpID,
EmpFirstName,
EmpLastName,
EmpGender,
EmpPhone
INTO tblEmployeeBackup
FROM tblEmployee
GO

we can see the result of above query in the following image.

3. Copy only filtered/selected rows into a new table.

We can copy selected/filtered rows into a new table by using SELECT * INTO Statement. For example, if we want to take backup of those employees who belong to Pakistan only, then we will write the query with WHERE clause as.

SELECT
   EmpID,
   EmpFirstName,
   EmpLastName,
   EmpGender,
   EmpPhone
INTO tblEmployeeBackup
FROM tblEmployee
WHERE EmpCountry='Pakistan'
GO

we can see the result of above query in the following image.

4. Copy columns from two or more tables into a new table

We can copy selected columns from two or more tables into a new table using SELECT * INTO Statement. First, we will joint two tables columns and then insert into a new table as.

SELECT
    e.EmpID,
    e.EmpFirstName,
    e.EmpLastName,
    e.EmpGender,
    e.EmpPhone,
    d.DeptName
INTO tblEmployeeBackup
FROM tblEmployee e
INNER JOIN tblDepartment d
ON d.DeptID=e.EmpDeptID
GO

we can see the result of above query in the following image.