[데이터베이스] MSSQL 다양한 DML 활용하기, 변수와 임시 테이블

UPDATE  + SET 절

열 값을 변수에 저장할 수 있어 후행 SELECT 쿼리가 불필요하다.

DECLARE @PVID int = 3150;
BEGIN TRAN

	SELECT PV_ID, UnitLength FROM dbo.ROAD WHERE PV_ID = @PVID;

	DECLARE @bLeng length, @aLeng length;

	UPDATE dbo.ROAD
		SET @bLeng = UnitLength
			, @aLeng = UnitLength = UnitLength * 1.15
	WHERE PV_ID = @PVID; 

	SELECT @bLeng AS bLeng, @aLeng AS aLeng;
	SELECT PVID, UnitLength FROM dbo.ROAD WHERE PV_ID = @PVID;

IF @@trancount > 0 ROLLBACK;
GO

 

INSERT/UPDATE/DELETE/MERGE + OUTPUT 절

DML 변경 (전/후) 행 데이터를 반환 할 수 있다. 

또한 단순 결과 데이터를 반환하거나, 테이블 입력 후 재활용 하는데 사용할 수 있다.    

=> Composable DML: 임시 테이블을 사용하지 않고, DML의 output을 활용할 수 있어 유용하다.

 

/* 변경 작업 수행 */
BEGIN TRAN

	/* 
	CategoryID = 5 제품 단가를 30% 올린 뒤, 이전 단가 15$ 이상이면서 이후 단가 50$이하 목록 
	*/
	INSERT INTO dbo.ProductsLog(ProductID, OldValue, NewValue)
	SELECT ProductID, OldValue, NewValue
	FROM (
		UPDATE dbo.Products
			SET UnitPrice *= 1.3
		OUTPUT 
			inserted.ProductID,
			deleted.UnitPrice AS OldValue,
			inserted.UnitPrice AS NewValue
		WHERE CategoryID = 5
	) AS D
	WHERE OldValue > 15.0 AND NewValue <= 50.0;

	SELECT * FROM dbo.ProductsLog;
  
IF @@trancount > 0 ROLLBACK;
GO

쿼리 결과

 

SELECT + INTO 절

CREATE TABLE + SELECT + INSERT 기능이다. 

쿼리의 결과를 신규 테이블로 복사 할 수 있는데, 대량의 데이터에서 필요한 데이터만 추출하여 활용할 때 유용하게 사용한다.

#, ## (전역) 을 사용하여 임시 테이블을 생성할 수 있으며, 열 이름, 데이터 형식, NULL 여부, identity 는 상속 받는다. 

하지만 제약조건, 인덱스, 트리거 등은 비상속 되어 추가 코드가 필요하다.

SELECT 
	ProductID, OrderID, Count, OrderDate = CAST(GETDATE() AS date)
INTO 
	#Orders
FROM 
	dbo.Orders
WHERE 
	ProductID <= 10250;

SELECT * FROM #Orders;

 

INSERT + EXEC 절

저장 프로시저나 동적 쿼리 결과를 테이블에 insert 할 수 있다. 

insert 쿼리를 반복 사용하면 성능 개선에 활용 가능하다. 

/* 
INSERT EXEC - 사용자 저장 프로시저
*/
SET IDENTITY_INSERT #Orders ON

INSERT #Orders (OrderID, OrderDate)
EXEC dbo.up_OrdersInfo @OrderID = 10250;

-- 데이터 확인.
SELECT * FROM #Orders;

SET IDENTITY_INSERT #Orders OFF


-- 정리
DROP PROC dbo.up_OrdersInfo;
DROP TABLE #Orders;