Search This Blog

Wednesday, October 20, 2010

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

1 comment:

Anonymous said...

Thanks mate!