Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cleaner T-SQL with Table Value Constructors

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

Comments

Posted by Kenneth Wymore on 29 June 2011

Pretty cool, good to know information. Is there a limitation to how many values you can insert at one time?

Posted by Steve Jones on 29 June 2011

Interesting, didn't know about the SELECT clause. Not sure I like that as cleaner, but it is worth playing with.

Posted by Jakub Dvorak on 30 June 2011

Yes, there is limitation of 1000 records inserted in one shot.

Jakub.

Leave a Comment

Please register or log in to leave a comment.