Row Constructor in SQL Server

Row constructor is the new feature introduced in SQL Server 2008 onward edition to insert multiple rows by writing a single INSERT statement.

Row constructor in SQL Server specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.

It saves a lot of time if you want to insert multiple records into a SQL Server table.

-- Before SQL Server 2008 – Multiple INSERT statements required.

INSERT INTO dbo.Employee (EmpID, EmpName)

VALUES (1, 'Jack')

INSERT INTO dbo.Employee (EmpID, EmpName)

VALUES (2, 'William')

INSERT INTO dbo.Employee (EmpID, EmpName)

VALUES (3, 'John')

-- SQL Server 2008 and after (Row constructors feature introduced)

INSERT INTO dbo.Employee (EmpID, EmpName) -- Single INSERT statement.

VALUES (1, 'Peter'), -- Row 1

(2, 'Gayle'), -- Row 2

(3, 'Daren') -- Row 3

Ebook Download
View all
Learn
View all