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