Using a TVC as a subquery in a join
declare @a as table (Item varchar(5), Quantity int)
insert into @a (Item, Quantity) values ('Item1', 4)
insert into @a (Item, Quantity) values ('Item1', 5)
insert into @a (Item, Quantity) values ('Item2', 6)
insert into @a (Item, Quantity) values ('Item2', 7)
select *
from @a Item
left join (
values
('Item2','Green'),
('Item3','Red')
) as t(Item, ItemColor) on t.Item = Item.Item
Basic use:
USE AdventureWorks2012;
GO
INSERT INTO Production.UnitMeasure
VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923');
GO
The following example demonstrates specifying DEFAULT and NULL when using the table value constructor to insert rows into a table.
USE AdventureWorks2012;
GO
CREATE TABLE Sales.MySalesReason(
SalesReasonID int IDENTITY(1,1) NOT NULL,
Name dbo.Name NULL ,
ReasonType dbo.Name NOT NULL DEFAULT 'Not Applicable' );
GO
INSERT INTO Sales.MySalesReason
VALUES ('Recommendation','Other'), ('Advertisement', DEFAULT), (NULL, 'Promotion');
SELECT * FROM Sales.MySalesReason;
The above examples show how to use a Table Value Constructor with an INSERT statement, here's the same thing with a SELECT statement
select MonthID,MonthName
from (VALUES
(1, 'January'),
(2, 'February'),
(3, 'March'),
(4, 'April'),
(5, 'May'),
(6, 'June'),
(7, 'July'),
(8, 'August'),
(9, 'September'),
(10, 'October'),
(11, 'November'),
(12, 'December')
) as mytable (MonthID,MonthName)
order by 1