Copy records from one table to another in SQL

Copy records from one table to another in SQL

Many times the following questions are asked regarding copying records from one table to another:

1. I need to copy the data from one table in one SQL S server to the same table in another SQL Server.

2. I want to insert data from one table to another table efficiently.

3. How to insert data from one table using where condition to another table?

The following methods can be used for this purpose:

There are two different ways to implement inserting data from one table to another table.

Method 1 : INSERT INTO SELECT

In this method the table is created earlier and the records are copied to the new table with INSERT INTO SELECT command.

----Create OnlineUsers Table

CREATE TABLE OnlineUsers(UserID varchar(10) NOT NULL, Name varchar(50) NOT NULL,

CONSTRAINT [PK_OnlineUsers] PRIMARY KEY CLUSTERED (UserID ASC))

----Create TempUsers Table

CREATE TABLE TempUsers (UserName VARCHAR(50) NOT NULL)

----INSERT RECORDS INTO TempUsers Table FROM OnlineUsers table using SELECT

INSERT INTO TempUsers (UserName)

SELECT Name FROM OnlineUsers

----Verify that Data in TestTable

SELECT UserName FROM TempUsers

----Clean Up Database

DROP TABLE TempUsers

GO

Method 2: SELECT INTO

In this method a new table is created with the same data types of table from where the records are being copied.

----Create new table and insert data into table using SELECT INSERT

SELECT Name

INTO TempUsers

FROM OnlineUsers

WHERE UserID < 5

----Verify that Data in TempUsers

SELECT Name

FROM TempUsers

----Clean Up Database

DROP TABLE TempUsers

GO