SQL Server 2008 - Row Constructors

There are times when we need to insert multiple rows into a table. SQL Server 2008 has row constructors which simplifies the data insertion process.

Here are the various ways we can insert multiple rows with a single INSERT statement

--Create a table

CREATE TABLE RealTable

(ID int, name varchar(10))

GO

-- Insert with VALUE clause

INSERT INTO RealTable Values (1,'Mary')

INSERT INTO RealTable Values (2,'Jim')

--Insert with Select clause

INSERT INTO RealTable SELECT 3, 'Hillary'

INSERT INTO RealTable SELECT 4, 'Lisa'

--Insert with UNION ALL

INSERT INTO RealTable

SELECT 5, 'Rick'

UNION ALL

SELECT 6, 'John'

UNION ALL

SELECT 7, 'Tom'

--Insert with Row Constructors (new in SQL Server 2008)

INSERT INTO RealTable (ID, Color)

VALUES

(8, 'Toni'),

(9, 'Joe'),

(10, 'dave')