As you know well, VALUES clause is used to insert values to the table
A simple example is
declare @t table (id int , col1 int, col2 int, col3 int)
insert into @t values(1, 14, 12, 24)
select * from @t
But in version 2008, you can do more things using VALUES clause than the previous versions.The following examples will explain it's various usages in detail
1 Insert multiple rows using a single INSERT statement
declare @t table (id int , col1 int, col2 int, col3 int)
insert into @t
values
(1, 14, 12, 24),
(2, 55, 31, 122),
(3, 121, 50, 28)
select * from @t
2 Simulate table variable
A table variable has a statement level scope ie you can run it for any number of times in a single session.Similarly you can use VALUES clause to simulate it.The following will explain you
select * from
(
values
(-1) ,
(1),
(32),
(523)
) as t (no)
The result is
no
-----------
-1
1
32
523
As you see a table is created with the name t and column n and it's scope is only for duration of the execution.You can use any number of columns with different datatypes as you see in the following examples
select * from
(
values
(-1,56) ,
(1,23),
(32,7123),
(523,632)
) as t (no1,no2)
The result is
no1 no2
----------- -----------
-1 56
1 23
32 7123
523 632
select * from
(
values
(1,'India','Chennai'),
(2,'India','Delhi'),
(3,'India','Mumbai'),
(4,'India','Bangalore')
) as t (id,country,city)
The result is
id country city
----------- ------- ---------
1 India Chennai
2 India Delhi
3 India Mumbai
4 India Bangalore
3 Replace CASE expression and UNPIVOT operator
Suppose you have many columns of same datatype and want to find minimum or maximum values out of them.You can use CASE espression or UNPIVOT operator for this as shown below
Source Data
declare @t table (id int , col1 int, col2 int, col3 int)
insert into @t select 1, 14, 12, 24
insert into @t select 2, 55, 31, 122
insert into @t select 3, 121, 50, 28
insert into @t select 4, 53, 67, 24
insert into @t select 5, 2, 39, 47
Solution 1 : CASE Expression
select id,
case
when col1>col2 and col1>col3 then col1
when col2>col3 then col2
else col3
end as maximum_value
from @t
Solution 2 : UNPIVOT Operator
select id, max(col) as maximum_value from
(
select id, col from @t
unpivot (col for cols in (col1,col2,col3)) as unpivott
) as p
group by id
Solution 3 : VALUES Caluse You can now use VALUES clause for the same task as shown below
select id,
(
select max(n) from
(
values(col1),(col2),(col3)
) as t(n)
) as maximum_value
from @t
As you see all the above produce the same following result
id maximum_value
----------- -------------
1 24
2 122
3 121
4 67
5 47