http://www.sqlservercentral.com/blogs/sqltreeo/2011/06/29/cleaner-t_2D00_sql-with-table-value-constructors/

Printed 2014/09/21 04:51AM

Cleaner T-SQL with Table Value Constructors

By Jakub Dvorak, 2011/06/29

Table Value Constructors were introduced in SQL Server 2008 and enables you to (not only) insert more records with one INSERT statement.

“Standard” option to insert more records is following:

INSERT INTO Continents (Name) VALUES ('Asia')
INSERT INTO Continents (Name) VALUES ('Africa')

With Table Value Constructors you can do this:

INSERT INTO Continents (Name)
VALUES ('Asia'),('Africa')

I must admin that I like it but there’s more. Sometimes you need list of constants in temporary set to be used just within your batch – I was always doing something similar to this (if there was only few values):

SELECT 'Africa'
UNION ALL
SELECT 'Asia'

With Table Value Constructors you can do this:

SELECT *
    FROM (VALUES ('Asia'), ('Africa')) AS Continents (Name)

You can even use subquery within VALUES command. Check full reference and limitations of Table Value Constructors on Technet.



Jakub Dvorak @ www.sqltreeo.com


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.