We know that stored data is very important for us and data backup is also very important and suppose that we need to keep backup of the data so we can use copy data from one table to another table to take the data backup In SQL Server 2008 R2.

Below I Have a table with [dbo].[tblEmployee] name:

CREATE TABLE[dbo].[tblEmployee]

(

   iEmpId int PRIMARY KEY IDENTITY,

   vEmpName varchar(100),

   vEmpContact varchar(15)

)

INSERT INTO [dbo].[tblEmployee] VALUES('Chris Roberts','+112-4356789')

INSERT INTO [dbo].[tblEmployee] VALUES('Brad Tutterow','+145-8437567')

INSERT INTO [dbo].[tblEmployee] VALUES('Paul Mendoza','+214-6444541')

INSERT INTO [dbo].[tblEmployee] VALUES('Zack Peterson','+875-55454547')

INSERT INTO [dbo].[tblEmployee] VALUES('Vijay Shukla','+91-9365894578')

Verify:

select * from [dbo].[tblEmployee]

Output:

Now I am creating a backup table with the name [dbo].[tblEmployeeBackup]

CREATE TABLE [dbo].[tblEmployeeBackup]

(

  iEmpID int,

  vEmpName varchar(100),

  vEmpContact varchar(15)

)

And now in this step below copy the data from [dbo].[tblEmployee] table to [dbo].[tblEmployeeBackup] table:-

INSERT INTO [dbo].[tblEmployeeBackup](iEmpID, vEmpName,vEmpContact )SELECT iEmpID, vEmpName,vEmpContact FROM [dbo].[tblEmployee] WHERE iEmpID = 1

Above query insert the data from main table to backup table below the output:

select * from [dbo].[tblEmployeeBackup]

Output:

Create a backup table and copy data simultaneously:

Now drop the backup table:

drop table [dbo].[tblEmployeeBackup]

Create and copy:

SELECT iEmpID, vEmpName,vEmpContact INTO [dbo].[tblEmployeeBackup] FROM [dbo].[tblEmployee] WHERE iEmpID = 1

Verify:

select * from [dbo].[tblEmployeeBackup]

Output: