INSERT INTO vs SELECT INTO

user_486997 0
Viewed 139401

What is the difference between using

SELECT ... INTO MyTable FROM...

and

INSERT INTO MyTable (...)
SELECT ... FROM ....

?

From BOL [ INSERT, SELECT...INTO ], I know that using SELECT...INTO will create the insertion table on the default file group if it doesn't already exist, and that the logging for this statement depends on the recovery model of the database.

  1. Which statement is preferable?
  2. Are there other performance implications?
  3. What is a good use case for SELECT...INTO over INSERT INTO ...?

Edit: I already stated that I know that that SELECT INTO... creates a table where it doesn't exist. What I want to know is that SQL includes this statement for a reason, what is it? Is it doing something different behind the scenes for inserting rows, or is it just syntactic sugar on top of a CREATE TABLE and INSERT INTO.

sql-server-2008 tsql sql-server
11 Answers

The other answers are all great/correct (the main difference is whether the DestTable exists already (INSERT), or doesn't exist yet (SELECT ... INTO))

You may prefer to use INSERT (instead of SELECT ... INTO), if you want to be able to COUNT(*) the rows that have been inserted so far.

Using SELECT COUNT(*) ... WITH NOLOCK is a simple/crude technique that may help you check the "progress" of the INSERT; helpful if it's a long-running insert, as seen in this answer).

[If you use...] INSERT DestTable SELECT ... FROM SrcTable ...then your SELECT COUNT(*) from DestTable WITH (NOLOCK) query would work.

user_11754960
Related