Table Value Constructors

Table Value Constructors for Insert Statement


CREATE TABLE test (

id INT,

name VARCHAR(100))


To insert data into this table here is the standard way.

INSERT INTO test
VALUES (1,'id1')

INSERT INTO test
VALUES (2,'id2')

INSERT INTO test
VALUES (3,'id3')

If you are fan of union all you can try this one also.

INSERT INTO test

SELECT 1,'id1'
UNION ALL
SELECT 2,'id2'
UNION ALL
SELECT 3,'id3'

IN SQL 2008 using Table Value Constructor you can write it more easily with less code.
When number of rows will grow , we will feel benefit of it because of less code.

INSERT INTO test

VALUES (1,'id1'),
(2,'id2'),
(3,'id3')

Even you can use it to create temporary derived table/CTE as shown below.

SELECT *
FROM (VALUES (1,'id1'),
(2,'id2'),
(3,'id3')
) testable(id,name)

Note:- You can not insert more than 1000 rows using this method in a single insert command.