INSERT INTO SELECT vs VALUES

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....


SELECT allows you test for existence first

INSERT Target (...)
SELECT keyvalue1, value2
WHERE NOT EXISTS (SELECT * FROM Target WHERE keycol = keyvalue1)

Or 2 sets of values

INSERT Target (...)
SELECT keyvalue1, value2
UNION ALL
SELECT keyvalue1a, value2a

Otherwise, there is no difference for a straight list of values and one row

If your values from from another table, then just INSERT..SELECT.. of course

Or mix'n'match:

INSERT Target (...)
SELECT col1, col2, @parameter1, @parameter2, col14
FROM Source