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?
4
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
1
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)