MSSQL: INSERT INTO vs SELECT INTO

The main diferrence is that INSERT INTO copy data into existing table
and SELECT INTO create new table and insert data into.

INSERT INTO SELECT FROM

The command to copy data from one table to another is:

INSERT INTO @TableToCopyData (Id, FirstName)
SELECT (Id, FirstName) FROM @Table

The whole example:

declare @TestTable table
(
	Id int,
	FirstName varchar(50)
)
-- Insert test data
INSERT INTO @TestTable (Id, FirstName) 
VALUES (1, 'Name1'), (2, 'Name2')

-- we create table where we copy data
declare @TableToCopyData table
(
	Id int,
	FirstName varchar(50)
)

INSERT INTO @TableToCopyData (Id, FirstName)
SELECT * FROM @TestTable

SELECT * FROM @TableToCopyData

SELECT INTO FROM

The command to create new table and copy data:

SELECT * INTO TableToCopyData 
FROM @TestTable

In this example the new table TableToCopyData will be created by SELECT INTO

declare @TestTable table
(
	Id int,
	FirstName varchar(50)
)

INSERT INTO @TestTable (Id, FirstName) 
VALUES (1, 'Name1'), (2, 'Name2')

SELECT * INTO TableToCopyData 
FROM @TestTable

SELECT * FROM TableToCopyData