Friday, October 31, 2014

SQL Derived Table from Values


So you have a small set of data you want to use in a query.  The data is static and temporary in nature so you don't really want to store it in a table.  A derived table from values might do the trick!

The FROM clause allows for a table_source clause of type derived_table (Table Value Constructor):

The derived_table can be structured like so:

(CAST('1/1/2014' AS date),'New Year''s Day'),
CAST('1/20/2014' AS date),'Martin Luther King, Jr. Day'),
CAST('5/26/2014' AS date),'Memorial Day'),
CAST('7/4/2014' AS date),'Independence Day'),
CAST('9/1/2014' AS date),'Labor Day'),
CAST('11/27/2014' AS date),'Thanksgiving Day'),
CAST('12/24/2014' AS date),'Christmas Eve'),
CAST('12/25/2014' AS date),'Christmas Day')
AS Holidays (HolidayDateHolidayName)

The resulting query:

The resulting table and columns are strongly typed making it easy to fill out the select clause and joins.

No comments:

Post a Comment

Please only include comments that add to the substance of the Blog Post:
- Question or request for clarification
- Idea for improvement
- Identifying an issue

Please refrain from promotion.