Microsoft SQL Server 2008 with its release included some new features that some of us (as developers) will find interesting and useful. In this article, which will be followed by others, I will try to shed some light on the row constructer feature (a nifty addition) as attributed to the family of T-SQL.

About The Feature

Ever spent time to fill a table with T-SQL insert statement where you have multiple records of the same format to insert into a single table. Well, row constructors come to the rescue. This feature allows you to effect multiple insertions of data using a single insert statement.

Requirements

In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise).

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Type the following listing to create your own temporary database (right click the connection node of the SQL server on the top and choose New Query Windows) and press F5.

Listing 1 - Create the temporary database

CREATE DATABASE [DbWork] ON PRIMARY 
(NAME = N'DbWork', 
FILENAME = 
N'C:\Program Files\Microsoft SQL 
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DbWork.mdf'
, SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
(NAME = N'DbWork_log', 
FILENAME = 
N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DbWork_log.ldf'
, SIZE = 1024KB , FILEGROWTH = 10%)
Go

3.    After Creating the DBWork, right click the connection node and choose refresh in order to display the newly created database.

4.    Right Click the newly created database and choose new query.

5.    Enter the listing found below to create your customer table as indicated below and press F5.

Listing 2 - Create the customer table in the database

USE [DbWork]
GO
CREATE TABLE [dbo].[TblCustomer](
      [CustomerId] [int] IDENTITY(1,1) NOT NULL,
      [CustomerName] [varchar](50) NOT NULL,
      [CustomerSince] [date] NOT NULL,
      [CustomerArea] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TblCustomer] PRIMARY KEY CLUSTERED 
(
      [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

6.    Clear the query windows after the statement has run.

7.    Now suppose that you have to insert the data listed in table below manually.

8.    I am going to show you two methods to do the job (With and Without row constructors).

9.    Without a row constructor method, the listing below should be used (Press F5 to execute).

Listing 3 - Create insert statement for every record

Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Nidal Arabi','12/31/2007','Hamra')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Kamal Diab','1/1/2008','Doha')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Nisrine Salem','2/1/2008','Quebec')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Hayat Salam','3/3/2008','Kuwait')
 
Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values ('Antoine Eid','4/4/2008','Morocco')

10. Now delete the content of the query window and enter the listing below to delete all records.

Listing 4 - Delete all records from the customer table

Truncate Table TblCustomer
Delete * From TblCustomer

The difference between delete and truncate is that the truncate Table statement does not log the deleted records in the SQL Server log file.

11. Now, using the row constructor feature, the listing is reduced to the following:

Listing 5 - Insert all records using the row constructor

Insert into TblCustomer (CustomerName, CustomerSince, CustomerArea)
Values      ('Nidal Arabi','12/31/2007','Hamra'),
            ('Kamal Diab','1/1/2008','Doha'),
            ('Nisrine Salem','2/1/2008','Quebec'),
            ('Hayat Salam','3/3/2008','Kuwait'),
            ('Antoine Eid','4/4/2008','Morocco')

12. Go ahead, try it in the query window and enjoy it.

Although the row constructor feature is nifty, you have to pay attention when using it. For instance, whenever a row in the values lines fails to execute, the whole statement would fail resulting in no insert of data. So be careful when you are using it, especially when writing code that executes prepared statements. See you in the next article with a new feature.