SQL Server – Insert Data From One Table to Another Table

There are two ways to insert data from one table to another table.

1. INSERT INTO SELECT  statement-

The INSERT INTO SELECT statement is used to copy data from one table and insert into another table.

• It is used when the table is already created in the database.
• If columns listed in insert clause and select clause are same then not required to list them in the query but if you are listed them, it's easy to understand.

We can also use WHERE condition in query to filter data from table.

Syntax:

-- for copy all columns from one table to another
INSERT INTO table1 
SELECT * FROM table2
Note: Number of columns must be same in both tables.

-- for copy selected columns from one table to another
INSERT INTO table1(COLUMN1, COLUMN2)
SELECT COLUMN1, COLUMN2 FROM table2

Example:

-- create new table 'tblStudent'
CREATE TABLE tblStudent(first_name nvarchar(100), last_name nvarchar(100))

-- insert data in to 'tblStudent' table from table 'tblPerson'
INSERT INTO tblStudent(first_name, last_name)
SELECT firstName, lastName FROM tblPerson WHERE profession = 'student'

-- check data is inserted in 'tblStudent' table
SELECT first_name, last_name FROM tblStudent

2. SELECT INTO statement-

The SELECT INTO statement first create table and then select data from one table and insert it into the another table.

• This method is used when the table is not created in the database and needs to be created when data inserted from another table. The new table is created with the same column name and data types as selected columns.

Syntax: 

SELECT COLUMN1, COLUMN2 INTO table1 FROM table2

Example:

-- create new table 'tblStudent' and insert data from table 'tblPerson'
SELECT firstName, lastName INTO tblStudent FROM tblPerson WHERE profession = 'student'

-- check data is inserted in 'tblStudent' table
SELECT first_name, last_name FROM tblStudent

Result: