SQL Server : Value Constructor Insight.

We all must be aware of VALUE Clause for Inserting values specified, in current blob we will explore different ways other then Insert were we can have use VALUE as handy.!

1. Generating Dummy Data!

we can use Value to generate tabular data for further joins or any functional usage.
we can have multiple column data as shown below.

SELECT TAB.COL1, TAB.COL2
FROM
(
VALUES (1,2),(3,4) ,(5,6)
) TAB(COL1,COL2);

Output :
COL1 COL2
----------- -----------
1 2
3 4
5 6

(3 row(s) affected)

2. Insert Multiple Records as one statement.

If can be done in a statement do it that way!
Instead of having multiple statement for same usage, it always efficient to accomplish same as single statement.

Using VALUES we can perform similar insert as one statement as compared to separate insert statement for each row..

CREATE TABLE TBL
(
C1 INT ,
C2 INT ,
C3 INT
);

INSERT INTO TBL(C1,C2 ,C3)
VALUES (1, 2, 3)
, (4, 5, 6)
, (7, 8, 9);

3. Get Max value from Set of Column using VALUES.

SELECT * ,
( SELECT MAX(COL) FROM (VALUES (C1) , (C2) , (C3) ) AS TAB(COL)) AS MAX_COL_VALUE
FROM TBL

OUTPUT:
c1 c2 c3 MAX_COL_VALUE
----------- ----------- ----------- -------------
1 2 3 3
4 5 6 6
7 8 9 9

4. SELECT within Values

We can have Select statement as part of VALUES.

INSERT INTO TBL(C1,C2 ,C3)
VALUES ((SELECT 1), (SELECT 2*7), (SELECT TOP 1 C1 FROM TBL))

(1 row(s) affected)

Hope it was a bit interesting and we learn something new.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Helper SQL, SQL Server Learning and tagged , . Bookmark the permalink.

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