There are basically two common ways to INSERT data from one table to another.
1) SELECT INTO
This is used when table is not previously created. New table is created when data from one table is transferred to the new table. The columns of the new table has same data type as selected columns from the old table.
USE AdventureWorks
GO
SELECT customerID, Title,FirstName, LastName
INTO dbo.TempTable
FROM SalesLT.Customer
WHERE Title=‘Ms.’
SELECT * FROM TempTable
2) INSERT INTO SELECT
We can use this when the destination table is already created.
USE AdventureWorks
GO
CREATE TABLE TempTable
(
CustomerID int not null,
Title nvarchar(8),
FirstName nvarchar(50) not null,
LastName nvarchar(50) not null
)
INSERT INTO TempTable
SELECT customerID, Title,FirstName, LastName
FROM SalesLT.Customer
WHERE Title=‘Ms.’
SELECT * FROM TempTable
I always prefer INSERT INTO method over SELECT INTO since SELECT INTO requires more lock resources.
Thanks,
Subhro Saha