Home » SQL SERVER – Insert Data From One Table to Another Table

SQL SERVER – Insert Data From One Table to Another Table

SQL SERVER – Insert Data From One Table to Another Table
There are two different ways to implement inserting data from one table to another table.

1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them but if different then you must always list them.

USE AdventureWorks
GO
----Create TestTable
CREATE TABLE Demo1 (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO Demo1 using SELECT
INSERT INTO Demo1 (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact

----Verify that Data in TestTable
SELECT FirstName, LastName
FROM Demo1
----Clean Up Database
DROP TABLE Demo1
GO

2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.

USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO Demo2
FROM Person.Contact

----Verify that Data in TestTable
SELECT FirstName, LastName
FROM Demo2
----Clean Up Database
DROP TABLE Demo2
GO

Both of the above method can be used with database temporary tables.

Leave a Reply

Your email address will not be published. Required fields are marked *