資料表值建構函式Values的用法

　　很特別的Value子句用法，2008之後的版本才有支援，先記起來，怕之後要用忘了怎麼用

　　根據BOL資料表值建構函式裡提到，資料表值建構函式可以在INSERT 陳述式的VALUES 子句、MERGE 陳述式的USING <來源資料表> 子句以及FROM 子句的衍生資料表定義中指定。也就是說可以用在三個地方！  

　　以下的例子多為BOL上的，自己稍微改一下加上補充說明囉

--1.用在Insert，可以用一個Insert插入多行，最多一個Insert插入行1000行

--建立測試用的資料表

USE AdventureWorks2008R2

GO

IF OBJECT_ID ('MyProducts', 'U') IS NOT NULL

   DROP TABLE dbo.MyProducts

CREATE TABLE dbo.MyProducts

(Name varchar(50),

 ListPrice money)

GO

--一次插入三行

INSERT INTO dbo.MyProducts (Name, ListPrice)

VALUES ('Helmet', 25.50),

       ('Wheel', 30.00),

       ('Phone', 45.00)

GO

select * from dbo.MyProducts

--Name ListPrice

--Helmet  25.50

--Wheel   30.00

--Phone   45.00

--2008以前的版本可以利用UNION ALL達到一樣的做法

INSERT INTO dbo.MyProducts (Name, ListPrice)

SELECT 'Helmet1', 25.50

UNION ALL SELECT 'Wheel1', 30.00

UNION ALL SELECT 'Phone1', 45.00

GO

select * from dbo.MyProducts

--Name ListPrice

--Helmet  25.50

--Wheel   30.00

--Phone   45.00

--Helmet1 25.50

--Wheel1  30.00

--Phone1  45.00

--VALUES子句還可以使用傳回單一欄位值的子查詢

INSERT INTO dbo.MyProducts (Name, ListPrice)

VALUES ('Book', 26.50),

       ('Pen', 18.00),

       ((SELECT Name FROM Production.Product WHERE ProductID = 720),

        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720))

GO

select * from dbo.MyProducts

--Name ListPrice

--Helmet  25.50

--Wheel   30.00

--Phone   45.00

--Helmet1 25.50

--Wheel1  30.00

--Phone1  45.00

--Book 26.50

--Pen 18.00

--HL Road Frame - Red, 52 1431.50

--像下面這樣多欄位值的子查詢就不能用，會報錯喔

INSERT INTO dbo.MyProducts (Name, ListPrice)

VALUES ('Book', 26.50),

       ('Pen', 18.00),

       (SELECT Name, ListPrice FROM Production.Product WHERE ProductID = 720)

GO

--2.用在FROM裡，像一個臨時表，但不用建立資料表變數

SELECT *

FROM

  (VALUES

    (1, 'Bikes'),

    (2, 'Accessories'),

    (3, 'Clothes')

  ) AS Category(ProductID, CategoryName)

--ProductID  CategoryName

--1 Bikes

--2 Accessories

--3 Clothes

--就可以拿來join啊

SELECT

  p.Name,

  Category.CategoryName

FROM

   Production.Product p INNER JOIN

  (VALUES

    (1, 'Bikes'),

    (2, 'Accessories'),

    (3, 'Clothes')

  ) AS Category(ProductID, CategoryName)

  ON p.ProductID = Category.ProductID

--Name CategoryName

--Adjustable Race  Bikes

--Bearing Ball  Accessories

--BB Ball Bearing  Clothes

--2008以前的版本，也可以這樣寫

SELECT

  p.Name,

  Category.CategoryName

FROM

   Production.Product p INNER JOIN

  (SELECT 1, 'Bikes'

   UNION ALL SELECT 2, 'Accessories'

    UNION ALL SELECT 3, 'Clothes'

  ) AS Category(ProductID, CategoryName)

  ON p.ProductID = Category.ProductID

--3.用在Merge裡，例子自己去資料表值建構函式看