SQL Server CTE 를 이용한 INSERT, UPDATE, DELETE, 재귀쿼리 예제

CTE (Common Table Expression) 이란?

임시로 이름이 지정된 결과 집합을 지정한다. CTE는 단순 쿼리에서 파생되며 반드시 SELECT, INSERT, UPDATE 또는 DELETE 문중 하나와 같이 쓰인다. 자신에 대한 참조를 포함하는 재귀 쿼리가 가능하고 CREATE VEIW 의 생성에 사용할수 있다. 

기존에 파생 테이블을 위해서 임시테이블이나 테이불 반환함수 또는 뷰를 사용하여야 했으나 CTE 를 이용하면 파생데이터에 대한 정확한 정의를 직관적으로 확인할수 있다.

상하, 선후 관계를 나타내기 위해 계층적 쿼리, 즉 재귀적 퀴리가 필요할경우 CTE 가 매우 유용하다. 

중복데이터 처리에 유용하다.

CTE 는 sql server 2005 버전부터 지원된다.

CTE 과 VIEW 의 차이점

VIEW 는 인덱스를 생성할수 있으나 CTE 는 인덱스를 생성할수 없다.

VIEW 는 데이터베이스 안에서 물리적 객체로 존재하기 때문에 나 이외의 다른 사용자들과 객체를 공유하여 쿼리할수 있기 때문에 유연하고 데이터베이스 단위에서 관리 된다.

CTE 테이블은 일회용 뷰와 비슷하다.

CTE 구문

 
[ WITH <common_table_expression> [ ,...n ] ]  

<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition ) 
 

expression_name : CTE 이름

column_name : AS 이하 쿼리에서 리턴할 칼럼들의 이름, 지정하지 않을경우 AS 이하 쿼리문의 첫번째 쿼리 칼럼명을 그래도 사용한다.

CTE_query_definition : 일반적인 뷰를 만들때 요구사항과 같은 제약을 갖는 SELECT 쿼리문을 사용할 수 있다.

CTE_query_definition 다음에 콤마(,) 로 구분하여 여러개의 CTE 를 하나의 WITH 구문 아래 작성하고 그아래  SELECT, INSERT, UPDATE 또는 DELETE 문중에서 사용할수 있다.

 

CTE 를 이용한 데이터 INSERT

사실상 기존 쿼리를 이용한 insert 방법과 큰 차이가 없다. CTE 자체가 다회사용이 불가능하기 때문에 기존 쿼리기반 insert....select... 를 대체할 필요를 느끼지 못한다. 

굉장히 복잡한 쿼리가 있어서 쿼리 내에서 CTE 를 여러번 참조해야 할 경우 유용할 수 있다

 
--CTE 를 이용한 INSERT

WITH test AS (

SELECT province, count(*) province_count

FROM a

GROUP BY province

)

INSERT INTO dbo.c (province, province_count)

SELECT * FROM test;
 
-------------------------------------------------------------

--기존 쿼리를 이용한 INSERT

INSERT INTO dbo.c (province, province_count)

SELECT province, count(*) province_count

FROM a

GROUP BY province

CTE 를 이용한 데이터 업데이트

 


if OBJECT_ID('test') is not null
DROP TABLE test
go
create table test(state varchar(100), city varchar(100), zipcode varchar(100))

insert into test(state, city, zipcode) values('GA','Peachtree City','30269-3428');
insert into test(state, city, zipcode) values('GA','Stockbridge','30281-5092');
insert into test(state, city, zipcode) values('GA','Milner','30257-3454');
insert into test(state, city, zipcode) values('GA','Atlanta','30326-1087');
insert into test(state, city, zipcode) values('GA','Atlanta','30339-3116');
insert into test(state, city, zipcode) values('GA','Fayetteville','30214-4544');
insert into test(state, city, zipcode) values('GA','Toccoa','30577-3860');
insert into test(state, city, zipcode) values('GA','Flowery Branch','30542-9998');
insert into test(state, city, zipcode) values('GA','Canton','30115-7255');
insert into test(state, city, zipcode) values('GA','Atlanta','30308-1881');
insert into test(state, city, zipcode) values('MN','St Paul','55125-9415');
insert into test(state, city, zipcode) values('MN','St Paul','55155-0001');
insert into test(state, city, zipcode) values('MN','New Prague','56071-2100');
insert into test(state, city, zipcode) values('MN','Eagan','55121-3530');
insert into test(state, city, zipcode) values('MN','Aitkin','56431-1337');
insert into test(state, city, zipcode) values('MN','Rochester','55902-1825');
insert into test(state, city, zipcode) values('MN','Minneapolis','55425-1628');
insert into test(state, city, zipcode) values('MN','Elk River','55330-1721');
insert into test(state, city, zipcode) values('MN','Bloomington','55431-1863');
insert into test(state, city, zipcode) values('MN','Edina','55439-2018');

if OBJECT_ID('test2') is not null
DROP TABLE test2
go

select distinct state, city into test2 from test;

alter table test2 add city_count int;

WITH test_cte AS (

SELECT city, city_count, (select count(*) from test where state=a.state and city=a.city) test_count

FROM test2 a

) 

update test_cte set city_count=test_count;
 

CTE 를 이용한 데이터 삭제 (CTE 를 이용한 중복제거)

오라클과 같이 명시적으로 ROWID를 쿼리 할수 없는 SQL SERVER 에서 중복데이터를 지우는게 쉽지 않았다. UNIQUE KEY 값을 가지고 있을때는 row_number() 나 rank() 함수를 이용하여 중복키 값을 쿼리하여 삭제할수 있으나 이마저도 없다면 IDENTITY 칼럼을 생성하여 UNIQUE 칼럼을 만들어 주거나 WITH IGNORE_DUP_KEY 옵션을 사용하여 중복제거된 새로운 테이블을 만들고 테이블을 교체하는 방법을 사용하였다.

하지만 CTE 쿼리를 이용할 경우 UNIQUE KEY 칼럼이 없다 할지라도 중복 데이터를 쉽게 삭제할 수 있다. JOIN 이나 UNION 등 CTE 쿼리에 멀티 테이블이 관련되지 않으면 삭제 쿼리를 수행할 수 있다.

 
if OBJECT_ID('test') is not null
DROP TABLE test
go

CREATE TABLE test(state varchar(100), city varchar(100), zipcode varchar(100))

INSERT INTO test(state, city, zipcode) VALUES('GA','Peachtree City','30269-3428');
INSERT INTO test(state, city, zipcode) VALUES('GA','Stockbridge','30281-5092');
INSERT INTO test(state, city, zipcode) VALUES('GA','Milner','30257-3454');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30326-1087');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30339-3116');
INSERT INTO test(state, city, zipcode) VALUES('GA','Fayetteville','30214-4544');
INSERT INTO test(state, city, zipcode) VALUES('GA','Toccoa','30577-3860');
INSERT INTO test(state, city, zipcode) VALUES('GA','Flowery Branch','30542-9998');
INSERT INTO test(state, city, zipcode) VALUES('GA','Canton','30115-7255');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30308-1881');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55125-9415');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55155-0001');
INSERT INTO test(state, city, zipcode) VALUES('MN','New Prague','56071-2100');
INSERT INTO test(state, city, zipcode) VALUES('MN','Eagan','55121-3530');
INSERT INTO test(state, city, zipcode) VALUES('MN','Aitkin','56431-1337');
INSERT INTO test(state, city, zipcode) VALUES('MN','Rochester','55902-1825');
INSERT INTO test(state, city, zipcode) VALUES('MN','Minneapolis','55425-1628');
INSERT INTO test(state, city, zipcode) VALUES('MN','Elk River','55330-1721');
INSERT INTO test(state, city, zipcode) VALUES('MN','Bloomington','55431-1863');
INSERT INTO test(state, city, zipcode) VALUES('MN','Edina','55439-2018');

go

WITH test_over_1city (state, city, zipcode, rnum) 
AS
(
SELECT state, city, zipcode, row_number() over(partition by state, city order by city) rnum

FROM test
)

DELETE test_over_1city WHERE rnum>1
;
 

row_number() 나 rank() 같은 함수를 사용하지 않더라도 조건을 이용하여 베이스 테이블의 데이터를 삭제할 수 있다.

 

if OBJECT_ID('test') is not null
DROP TABLE test_member
go

CREATE TABLE test(state varchar(100), city varchar(100), zipcode varchar(100))

INSERT INTO test(state, city, zipcode) VALUES('GA','Peachtree City','30269-3428');
INSERT INTO test(state, city, zipcode) VALUES('GA','Stockbridge','30281-5092');
INSERT INTO test(state, city, zipcode) VALUES('GA','Milner','30257-3454');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30326-1087');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30339-3116');
INSERT INTO test(state, city, zipcode) VALUES('GA','Fayetteville','30214-4544');
INSERT INTO test(state, city, zipcode) VALUES('GA','Toccoa','30577-3860');
INSERT INTO test(state, city, zipcode) VALUES('GA','Flowery Branch','30542-9998');
INSERT INTO test(state, city, zipcode) VALUES('GA','Canton','30115-7255');
INSERT INTO test(state, city, zipcode) VALUES('GA','Atlanta','30308-1881');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55125-9415');
INSERT INTO test(state, city, zipcode) VALUES('MN','St Paul','55155-0001');
INSERT INTO test(state, city, zipcode) VALUES('MN','New Prague','56071-2100');
INSERT INTO test(state, city, zipcode) VALUES('MN','Eagan','55121-3530');
INSERT INTO test(state, city, zipcode) VALUES('MN','Aitkin','56431-1337');
INSERT INTO test(state, city, zipcode) VALUES('MN','Rochester','55902-1825');
INSERT INTO test(state, city, zipcode) VALUES('MN','Minneapolis','55425-1628');
INSERT INTO test(state, city, zipcode) VALUES('MN','Elk River','55330-1721');
INSERT INTO test(state, city, zipcode) VALUES('MN','Bloomington','55431-1863');
INSERT INTO test(state, city, zipcode) VALUES('MN','Edina','55439-2018');

go

WITH test_over_1city (state, city, zipcode, rnum) 
AS
(
SELECT state, city, zipcode, row_number() over(partition by state, city order by city) rnum

FROM test
)

DELETE test_over_1city WHERE zipcode like '559%'
;

오라클의 Connect_by_prior 에 대응하는 CTE 재귀쿼리

재귀쿼리 구문

 
WITH cte_alias (column_aliases)  
AS  
( 
cte_query_definition   --initialization 
UNION ALL 
cte_query_definition2 --recursive execution 
)  
SELECT * FROM cte_alias ;
 

재귀쿼리의 실제 적용예

WITH Managers AS 
( 
--initialization 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers  
 

무한 루프를 막기위한 옵션

 
SELECT * FROM Managers OPTION (MAXRECURSION 4)