VALUES의 괄호 안에 올 수 있는 값은 상수, 변수 또는 식입니다(단, 식은 EXECUTE 문을 포함할 수 없습니다). 따라서 다음과 같이 사용자 정의 함수를 사용할 수도 있습니다.
INSERT INTO MyTable
VALUES (1, dbo.MyFunc(10, 20)),
(2, dbo.MyFunc(30, 40))
식이 가능하기 때문 다른 SELECT문이 포함될 수도 있습니다.
INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
('Wheel', 30.00),
((SELECT Name FROM Production.Product WHERE ProductID = 720),
(SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
SELECT .. FROM (VALUES)
INSERT뿐 아니라 SELECT의 FROM절에서도 VALUES를 사용할 수 있는데 일종의 가상 파생 테이블과 같다고 볼 수 있겠습니다.
SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
실행결과
앞서 INSERT .. VALUES에서 설명드린 것처럼 하나의 행을 하나의괄호로 묶고 쉼표로 구분하면 됩니다.
CROSS APPLY (VALUES)
CROSS APPLY 의 VALUES에서 공급받은 행의 값을 이용하여 다양하게 활용할 수 있습니다.
CREATE TABLE #temp
(
col1 int
, col2 int
, col3 int
, col4 int
, col5 int
)
INSERT #temp VALUES(1,2,3,4,5), (10,20,30,40,50)
SELECT
CASE WHEN a.col1 = b.col THEN b.col END AS col1,
CASE WHEN a.col2 = b.col THEN b.col END AS col2,
CASE WHEN a.col3 = b.col THEN b.col END AS col3,
CASE WHEN a.col4 = b.col THEN b.col END AS col4,
CASE WHEN a.col5 = b.col THEN b.col END AS col5
FROM #temp a
CROSS APPLY (VALUES (a.col1),(a.col2),(a.col3),(a.col4),(a.col5)) b (col)
실행결과
CREATE TABLE #temp
(
col1 int
, col2 int
, col3 int
, col4 int
, col5 int
)
INSERT #temp VALUES(1,2,3,4,5), (10,20,30,40,50)
SELECT
a.*, b.total
FROM
#temp a
CROSS APPLY
(
SELECT SUM(x) total
FROM (VALUES (a.col1), (a.col2), (a.col3), (a.col4), (a.col5)) MyTable (x)
) b
실행결과
MERGE .. USING (VALUES)
MERGE문의 USING에 VALUES를 통해서 파생 원본 테이블로 사용할 수 있습니다.
MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;
(응용) 열 데이터에 대하여 집계 함수 사용
다음과 같이 열 데이터를 행으로 전환하는데 VALUES를 이용하여 한 행의 여러 칼럼 값들에 대하여 집계 함수를 사용할 수 있습니다.
CREATE TABLE #temp
(
col1 int
, col2 int
, col3 int
, col4 int
, col5 int
)
INSERT #temp VALUES(4,1,8,10,57), (17,21,130,12,48)
SELECT
col1, col2, col3, col4, col5
, ( SELECT AVG(a)
FROM (VALUES (col1), (col2), (col3), (col4), (col5)) x(a)
) average
FROM #temp
