Store data or dataset in SQL server without create table (Row Constructors in SSMS)

Row Constructors in Sql server provides an option to create data set without creating table in database. It has introduced from SSMS 2008 version.

Now will see, how to do that…

Here we will create a set of data with two columns named “Id” and “Name”. The data container (dataset) name will give “Std_details”

select * FROM (VALUES (1,'Pritam'),(3,'Lisa'),(4,'Piyali')) AS Std_details(Id,Name)

Output:

We can do join with such type of queries with table. For example I have mentioned below

There is another set of data named "Marks_details"

select * from (VALUES (1,500,240),(3,500,300),(4,500,450)) AS Marks_details(Id,Total_Marks,Scored_Marks)

Output:

Now will perform Inner join between two different set of data.

SELECT Std_details.*,Marks_details.Total_Marks,Marks_details.Scored_Marks

FROM (VALUES (1,'Pritam'),(3,'Lisa'),(4,'Piyali')) AS Std_details(Id,Name)

Inner join   (VALUES (1,500,240),(3,500,300),(4,500,450)) AS

Marks_details(Id,Total_Marks,Scored_Marks)

on Std_details.Id=Marks_details.id

Output: