SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors

Row Constructors allow you to insert mulitiple records with a single INSERT statement. Well, you could always do that with an "INSERT INTO tablename (fieldlist) SELECT fieldlist" syntax if the values being inserted is based on a table. However, if you have values as static strings or variables, with the previous versions of SQL Server, you always needed multiple insert statements. Let us look at an example that demonstrates this.

Let us create a table that holds employee information.

-- Create a TEMP table
CREATE TABLE #Employees(
  EmpID INT IDENTITY,
  FirstName VARCHAR(20),
  LastName VARCHAR(20)
)

My favorite way of inserting values is by using the SELECT syntax as given below.

-- Option 1 : using SELECT
INSERT INTO #Employees (FirstName, LastName)
SELECT 'Jacob', 'Sebasian'
INSERT INTO #Employees (FirstName, LastName)
SELECT 'Bob', 'Jones'

But a more common practice that I notice is by using the VALUES syntax.

-- Option 2 : using VALUES
INSERT INTO #Employees (FirstName, LastName)
VALUES ('Jacob', 'Sebasian')
INSERT INTO #Employees (FirstName, LastName)
VALUES ('Bob', 'Jones')

In the examples given above, we are executing two separeate INSERT statements to load two rows to our table. Some times, for performance reasons, you might prefer to go with a single statement that loads all the required rows into the table. One way to achieve this is by generating a set using UNION ALL and then inserting it to the table using the SELECT syntax. Here is an example:

-- Option 3 : using SELECT with UNION ALL
INSERT INTO #Employees (FirstName, LastName)
SELECT 'Jacob', 'Sebasian'
UNION ALL
SELECT 'Bob', 'Jones'

SQL Server 2008 (KATMAI) introduces a more compact syntax to get this done by using Row Constructors. Here is the SQL Server 2008 version of the above code.

-- Option 4 : using ROW CONSTRUCTOR
INSERT INTO #Employees (FirstName, LastName)
VALUES ('Jacob', 'Sebasian'),
     ('Bob', 'Jones')

I think I like it. Do you?

SQL Server 2008 (KATMAI) TSQL Enhancements

1. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 1 - Variable initialization and Compound Assignment Operators
2. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 2 - Row Constructors
3. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 3 - New Data Type: DATE
4. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 4 - New Data Type: TIME
5. SQL Server 2008 (KATMAI) TSQL Enhancements - Part 5 - New Data Type: DATETIME2