Difference between select into and insert into

Select Into ->Creates the table on the fly upon select execution

while

Insert Into ->Presumes that the Table DB already exist

lastly

Create, simply creates the table from the return result of the query

here are some very important differences between SELECT INTO and INSERT.

First, for the INSERT you need to pre-define the destination table. SELECT INTO creates the table as part of the statement.

Second, as a result of the first condition, you can get type conversion errors on the load into the table using INSERT. This cannot happen with a SELECT INTO (although the underlying query could produce an error).

Third, with a SELECT INTO you need to give all your columns names. With an INSERT, you do not need to give them names.

Fourth, SELECT INTO locks some of the metadata during the processing. This means that other queries on the database may be locked out of accessing tables. For instance, you cannot run two SELECT INTO statements at the same time on the same database, because of this locking.

Fifth, on a very large insert, you can sometimes see progress with INSERT but not with SELECT INTO. At least, this is my experience.

When I have a complicated query and I want to put the data into a table, I often use:

SELECT top 0 *
INTO <table>
FROM <query>

INSERT INTO <table>
    SELECT * FROM <query>