Home > DeveloperSection > Articles > Copy data from one table to another table in SQL Server 2008 R2

Copy data from one table to another table in SQL Server 2008 R2


Database Database 
Ratings:
0 Comment(s)
 6806  View(s)
Rate this:

Copy data from one table to another table in SQL Server 2008 R2

In this article I am trying to explain how to copy data from one table to another and how to copy data as well as create table in SQL Server 2008 R2.

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:

Copy data from one table to another table in SQL Server 2008 R2

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:

Copy data from one table to another table in SQL Server 2008 R2

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:

Copy data from one table to another table in SQL Server 2008 R2


Don't want to miss updates? Please click the below button!

Follow MindStick