INSERT INTO SELECT vs VALUES

Question

Although there is no reason (apart maybe from aesthetics) to use INSERT INTO SELECT when inserting a single row in a table, is there any difference between using this and INSERT INTO VALUES?

Answer 1

Using the INSERT INTO ... SELECT approach allows you to pick your values from another table, based on some criteria.

INSERT INTO dbo.TargetTable(Col1, Col2, ...., ColN)    SELECT Col1, Col2, ..., ColN    FROM dbo.SourceTable    WHERE (some condition) 

That might be a bit easier and more readable to write, rather than having to retrieve 20 values from your source table, stash them into temporary variables, just so you can then call your INSERT INTO dbo.Destination(....) VALUES(......) statement...

DECLARE @Value1 INT DECLARE @Value2 DATETIME .... DECLARE @ValueN INT  SELECT        @Value1 = Col1,       @Value2 = Col2,       ....       @ValueN = ColN FROM      dbo.SourceTable WHERE      (some condition)   INSERT INTO dbo.TargetTable(Col1, Col2, ...., ColN) VALUES(@Value1, @Value2, ....., @ValueN) 

But in the end - it's just an INSERT statement that inserts data - it's really just a matter of personal preference and which approach is easier / more convenient to use....