Printed 2017/08/17 01:42PM

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'

With Table Value Constructors you can do this:

    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 @

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.