Archive

Posts Tagged ‘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.

Follow

Get every new post delivered to your Inbox.