Home > CodeProject, Sql Server > New Feature In SQL Server 2008: Row Constructor

New Feature In SQL Server 2008: Row Constructor

I think we all are familiar with INSERT statement in Sql Server 7.0/2000/2005, and may be in Oracle, MySQL, etc. In Sql Server, we have come across INSERT INTO TableName (columns) VALUES (literalValues),  or INSERT INTO TableName (columns) SELECT columns FROM TableNameX statement many times. The first type of statement is used when we have to insert one record at a time, while the second statement helps us to insert one or more records depending upon the number of rows returned by SELECT statement. Some even use UNION clause to select multiple rows.

We also know how painful it was to insert multiple records using  INSERT INTO TableName (columns) VALUES (literalValues) from DataAccessLayer although different programmers have different approaches to it.

But Sql Server 2008 has now come with a rescue for all programmers with a new feature called “Row Constructor“. Yes, a Class like initialization constructor!

Let’s see it by example.

Example 1:
–Create a new table [BusinessClients] with row values from the list [WHERE Age <= 30]
SELECT Name, Email, Profession, ContactNo
INTO dbo.BusinessClients
FROM
(
VALUES(‘Dinesh’, ‘dkm@hotmail.com’,’Software Engineer’, ‘9899144254’, 28),
(‘Ravindra’, ‘rg@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),
(‘Rosan’, ‘rs@who.com’, ‘Social Activist’, ‘9891234560’, 30),
(‘Hari’, ‘ha@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)
) as Employee(Name, Email, Profession, ContactNo, Age)
WHERE Age <= 30;
Go

–Fetch the inserted records from a newly created table
Select * from dbo.BusinessClients;
Go

Example 2:
–Select records from the row constructors
SELECT Name, Email, Profession, ContactNo
FROM
(
VALUES(‘DineshMandal’, ‘dinesh.kumar.mandal@hotmail.com’,’Software Engineer’, ‘9899144254’, 28),
(‘RavindraGhimire’, ‘ravindra@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),
(‘RosanSapkota’, ‘rosan@who.com’, ‘Social Activist’, ‘9891234560’, 30),
(‘HariAdhikari’, ‘hari@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)
) as EmployeeDetails(Name, Email, Profession, ContactNo, Age)
WHERE Age <= 30;
Go

See the VALUES clause in parenthesis and separated by comma. This is what we call “Row Constructor”. In example 2, see the derived table name EmployeeDetails and its column names inside the parenthesis. And of course the list of column names we like to select out of VALUES parameter values. These are few things we should take care when using row constructor.

Result of Example 2:
SQL Insert

I think this feature is really praiseworthy.

<!–[if gte mso 9]> Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 <![endif]–><!–[if gte mso 9]> <![endif]–> <!–[endif]–>

I think we all are familiar with INSERT statement in Sql Server 7.0/2000/2005, and may be in Oracle, MySQL, etc. In Sql Server, we have come across INSERT INTO TableName (columns) VALUES (literalValues), or INSERT INTO TableName (columns) SELECT columns FROM TableNameX many times. The first type of statement is used when we have to insert one record at a time, while the second statement helps us to insert one or more records depending upon the number of rows returned by SELECT statement. Some even used UNION clause to select multiple rows.

We also know how painful it was to insert multiple records using INSERT INTO TableName (columns) VALUES(literalValues) from DataAccessLayer although different programmers have different approaches to it.

But Sql Server 2008 has now come with a rescue for all programmers with a new feature called “Row Constructor”. Yes, a Class like initialization constructor!

Let’s see it by example.

Example 1:

–Create a new table [BusinessClients] with row values from the list [WHERE Age <= 30]

SELECT Name, Email, Profession, ContactNo

Into dbo.BusinessClients

FROM

(

VALUES(‘Dinesh’, ‘dkm@hotmail.com’,‘Software Engineer’, ‘9899144254’, 28),

(‘Ravindra’, ‘rg@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),

(‘Rosan’, ‘rs@who.com’, ‘Social Activist’, ‘9891234560’, 30),

(‘Hari’, ‘ha@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)

) as Employee(Name, Email, Profession, ContactNo, Age)

WHERE Age <= 30;

Go

–Fetch the inserted records from a newly created table

Select * from dbo.BusinessClients;

Go

Example 2:

–Select records from the row constructors

SELECT Name, Email, Profession, ContactNo

FROM

(

VALUES(‘DineshMandal’, ‘dinesh.kumar.mandal@hotmail.com’,‘Software Engineer’, ‘9899144254’, 28),

(‘RavindraGhimire’, ‘ravindra@example.com’, ‘Telecom Engineer’, ‘9887345678’, 28),

(‘RosanSapkota’, ‘rosan@who.com’, ‘Social Activist’, ‘9891234560’, 30),

(‘HariAdhikari’, ‘hari@rnac.com’, ‘Construction Engineer’, ‘9897654890’, 32)

) as EmployeeDetails(Name, Email, Profession, ContactNo, Age)

WHERE Age <= 30;

Go

See the VALUES clause in parenthesis and seperated by comma. This is what we call “Row Constructor”. In example 2, see the derived table name EmployeeDetails and its column names inside the parenthesis. And of course the list of column names we like to select out of VALUES parameter values. These are few things we should take care when using row constructor.

Result of Example 2:

I think this feature is really praiseworthy.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: