Enhanced VALUES Clause (ROW Constructor) – SQL SERVER 2008 / R2

 Old Style:

If you see at previous versions of SQL Server (Older than 2008), you were able to insert only a single row by using values clause.

INSERT INTO dbo.mytable(id, val) VALUES(1, 'First Value');

Or

If you were inserting through SELECT statement, you were able to specify a single row values or by combining multiple rows by using UNION/UION ALL operator.

INSERT INTO dbo.mytable(id, val)

SELECT 1 AS ID, 'First Value' AS VAL

UNION ALL SELECT 2, 'Second Value'

UNION ALL SELECT 3, 'Thrid Value'

New Style:

Now, using SQL Server 2008 or Later, you can find enhanced version of VALUES clause that works as a collection of row(s). You can specify up to 1000 rows by using this clause. Further, it treats it a single transaction, in case of failure, all rows will be rolled back.

Try this example:

CREATE TABLE dbo.mytable

(ID INT,

 VAL VARCHAR(100)

 );

 -- Inertion by using Values clause

 INSERT INTO dbo.mytable (ID, VAL )

 VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value')

-- Insertion by using Values clause with Select Statement

 INSERT INTO dbo.mytable (ID, VAL )

 SELECT *

 FROM (VALUES (1, 'First Value')

 ,(2, 'Second Value')

 ,(3, 'Third Value') ) as temp(ID, VAL)

Further, you can use VALUES clause to parse a string of multiple parameter values to get them in rows.

Try below script:

DECLARE @StrValues NVARCHAR(100)

DECLARE @Str NVARCHAR(500)

SET @StrValues = '(''A''),(''B''),(''C'')'

SET @Str = 'SELECT * FROM (VALUES ' + @StrValues + ' ) as Temp(Col1)'

EXEC SP_EXECUTESQL @Str