Insert Into Table From Another Table in SQL Server

Sometimes you may need to copy data from one table to another table in SQL Server. You can easily do this in a single SQL query. In this article we will look at how to insert into table from another table in SQL Server.

Insert Into Table From Another Table in SQL Server

You can copy data from one table into another table using INSERT INTO statement.

Here is the syntax of INSERT INTO statement.

If you want to copy all columns from one table to another table.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

In the above SQL query, table1 is the source table and table2 is the target table. You can optionally specify a WHERE clause if you want to copy only some rows from table1 to table2.

Also read : How to Update Multiple Columns in SQL Server

If you want to copy only some columns from one table to another table.

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

In the above SQL query, you need to specify the column names in both source and target tables between whom you want to copy data. Please note, the number and order of columns for both tables should be the same, so that the data is copied correctly.

Also, please note, in both cases, the original source table remains unaffected.

Also read : How to Insert Data in SQL Server

Insert Into Examples

Let us say you have a table sales(id, order_date, amount)

mysql> create table sales(id int, order_date date, amount int);

mysql> insert into sales(id,order_date, amount)
       values(1,'2020-12-01',100),
             (2,'2020-12-02',150),
             (3,'2020-12-03',200);

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    100 |
|    2 | 2020-12-02 |    150 |
|    3 | 2020-12-03 |    200 |
+------+------------+--------+

Here is the SQL query to copy all data from sales table into sales2 table.

mysql> create table sales2(id int, order_date date,amount int);

mysql> insert into sales2
       select * from sales;

mysql> select * from sales2;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2020-12-01 |    100 |
|    2 | 2020-12-02 |    150 |
|    3 | 2020-12-03 |    200 |
+------+------------+--------+

Also read : How to Create View in SQL Server

Here is the SQL query to copy only specific rows from sales to sales2.

mysql> insert into sales2
        select * from sales
        where id>1;

mysql> select * from sales2;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    2 | 2020-12-02 |    150 |
|    3 | 2020-12-03 |    200 |
+------+------------+--------+

Here is the SQL query to copy only columns id and amount from sales to sales2 table.

mysql> insert into sales2 (id,amount)
       select id, amount from sales;

mysql> select * from sales2;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | NULL       |    100 |
|    2 | NULL       |    150 |
|    3 | NULL       |    200 |
+------+------------+--------+