differences between INSERT select and insert into select?

Question

Hello everyone,

I often see two styles, INSERT select and insert into select, what are the differences? Are they the same?

I am using SQL Server 2008 Enterprise.

Here are two samples.

INSERT california_authors (au_id, au_lname, au_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE State = 'CA'

http://www.sqlteam.com/article/using-select-to-insert-records

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information

http://www.1keydata.com/sql/sqlinsert.html

thanks in advance, George

Answer 1

They're the same; the INTO is just optional.

Answer 2

In SQL Server and MySQL, INTO is pure syntax sugar.

No difference.

Oracle and PostgreSQL require this keyword to be present, and, AFAIR, ANSI SQL standards do too.

Answer 3

I believe the two statements you have listed are in fact the same. The "INTO" statement is missing in the first batch but it is implied as part of the INSERT statement.

For illustration purposes, here are some different forms of using the INSERT statement.

SELECT 
    1 AS ID ,
    'SomeText' AS SomeText
INTO #tmpTableName

INSERT INTO #tmpTableName (ID,SomeText) 
SELECT 2, 'Text' 

INSERT INTO #tmpTableName (ID,SomeText) 
VALUES(3, 'MoreText')

SELECT * FROM #tmpTableName

DROP TABLE #tmpTableName