Is there a difference between SQL INSERT patterns

Question

SQL inserts are usually documented like so:

INSERT TableName (Column1, Column2) VALUES ('Value1', 'Value2')

However, in TSQL it's also possible to write the (seemingly) same statement like so:

INSERT TableName (Column1, Column2) SELECT 'Value1', 'Value2'

Explain Plan seems to think they're identical.

Are they? Is there any hidden difference or any argument for doing it the INSERT () Values () way?

• FrustratedWithFormsDesigner: The only difference I can see is that the first one is clearer and easier to understand. Why do a SELECT in an INSERT if you're not doing a SELECT INTO?
• Robert Harvey: If EXPLAIN says they're the same, they're effectively the same. I'd prefer the first form for clarity, however.
• JFTxJ: It is true that if the Explain Plan says they are the same, they are the same. But that doesn't mean that they would always be the same. For example, if your SELECT statement (2nd example) returned multiple rows, the result plan would not be the same. Remember that SQL optimizer is smart enough to know that you have 2 constants in your select. He WILL turn that into a strait VALUES statement. The main difference is that the first syntax only supports static (or constant) values. Your second syntax will allow you to insert 1 or many rows, depending on the result of your query.
• JFTxJ: To answer your actual question, the only difference I ever saw was that I found it easier and clearer when generating insert statements (macro generating SQL statements for data migration for example) to use the INSERT () VALUES () syntax... But no real advantage....

Answer 1

The difference is not hidden - simply your examples don't illustrate it :-)

Using INSERT TableName (Column1, Column2) VALUES ... syntax limits your options to values lists (as in your examples; note that the value list should be enclosed in parentheses.) or return value of stored procedure e.g.

INSERT INTO Cities (Location) VALUES ( dbo.CreateNewPoint(x, y) );

Using INSERT TableName (Column1, Column2) SELECT ... syntax is much more powerful. In addition to cases above you can insert almost any result of SELECT statement if only it matches target columns types.

In particular you can insert data from other tables, mix values from multiple tables with constant values etc. For example:

INSERT INTO TargetTable (name, ColumnA, ColumnB, Sum) 
SELECT 'Programmers', T1.Value, T2.Value, SUM(T1.Value, T2.Value)
FROM SourceTable1 T1, SourceTable2 T2 WHERE T1.Value > 0 AND T2.Value > 0;

Please note that INTO is an optional keyword that can be used between INSERT and the target table.

Reference: https://msdn.microsoft.com/en-us/library/ms174335.aspx

• JC Ford: Definitely far more powerful. I was limiting the examples intentionally. I use INSERT...SELECT frequently when inserting data from other tables. I tend to also use it when inserting a single row that isn't drawn from another table, but every time, I hear a tiny voice in my head saying "You're supposed to use VALUES() for that!" Based on the feedback so far here, I feel better about telling that tiny voice to take a hike.

Answer 2

In SQL there are many times where different syntax do the same thing

At just one static insert then no difference

Clearly the limit of values is static (hard coded) values
You cannot reference a table

A select statement can be used to present static (hard coded) value as you have shown. And you can even combine hard coded with columns in a select from.

What you are missing is the s in values

INSERT TableName (Column1, Column2) 
VALUES ('Value1a', 'Value2a')
 , ('Value1b', 'Value2b')

The above is more efficient than multiple insert statements

With select the syntax is

INSERT TableName (Column1, Column2) 
select 'Value1a', 'Value2a'
union 
select 'Value1b', 'Value2b'

In this case values is more convenient (in my opinion)