Search code examples
sqlsql-server-2005insert

INSERT INTO SELECT vs VALUES

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?

Solution

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