출처 : https://da-new.tistory.com/171

 

[MsSQL] OUTPUT - UPDATE 사용 시, 변경 전/후 행 반환하기 (deleted/inserted)

OUTPUT Output 구문은 insert, update, delete, merge 문과 함께 사용되며, 각 DML문에 처리되는 행을 반환하는 문법이다. 각 행을 반환하여, Into 구문를 통해 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다. Update 문

da-new.tistory.com

 

 

 

 

OUTPUT

Output 구문은 insert, update, delete, merge 문과 함께 사용되며, 각 DML문에 처리되는 행을 반환하는 문법이다.

각 행을 반환하여, Into 구문를 통해 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

 

Update 문 사용 시 Output 

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
  -- into 구문
    into TABLE_B (b_column1, b_column2, b_column3)
   where [조건]

 

update문 사용시, update ~set과 조건 문 사이에 output, into 구문이 위치한다.

 

deleted 접두사

Update Output 문에는 output 시, deleted 라는 접두사와 아래 설명할 inserted라는 접두사를 사용 할 수 있다.

 

deleted 접두사는 타겟이 되는 TABLE_A의 row의 변경 전 혹은 삭제가 될 경우 해당 행을 반환하는 접두사이다.

 

Update 문에서는 해당 row의 컬럼이 변경 되므로 변경 전 행이 반환된다.

변경되어 사라지기에 deleted 라고 생각하면 조금 더 이해하기 편하다.

 

inserted 접두사

Update Output 문에서 output 시, deleted라는 접두사 뿐만 아니라 inserted라는 접두사도 사용이 가능하다

 

inserted 접두사는 타겟이 되는 TABLE_A의 row의 변경 후 혹은 삽입이 될 경우 해당 행을 반환하는 접두사이다.

 

 

위에 설명한 것과 마찬가지의 개념으로 Update 문에서는 해당 row의 컬럼이 변경 되므로 변경 후 행이 반환된다.

변경되어 새롭게 바뀌어 추가 되기에 inserted라고 생각하면 조금 더 이해하기 편하다.

 

output 구문

실행 쿼리 1

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
   where [조건]

output 구문은 이렇게 update ~ set과  조건문 사이에 위치하게 된다.

update 문에서 output 구문을 단독으로 사용 시,

변경 전 행들은 deleted, 변경 후 행들은 inserted 접두사를 통해 output 구문으로 반환된다.

 

위 사진의 쿼리를 실행시 아래와 같이 반환됨을 알 수 있다.

반환 값 1

여기서 중요한 것은 deleted / inserted를 혼합하여 사용 할 수 있다는 점인데,

두개를 활용하여 변경 전 / 후의 연산으로 값을 비교한다던지의 활용을 할 수 있다.

 

조건문이 없거나, 조건에 해당하는 값이 여러개 일 경우 한 행만 반환되는 것이 아니라, 삭제된 모든 행이 반환된다.

실행 쿼리 2

조건문 없이 TABLE_A의 모든 행을 변경 할 경우 아래와 같이 TABLE_A의 변경 된 모든 값들을 반환한다.

into 구문

  update TABLE_A
     set a_column1 = 'value3'
  -- output 구문
  output deleted.a_column1, inserted.a_column2, deleted.a_column3 + inserted.a_column3
  -- into 구문
    into TABLE_B (b_column1, b_column2, b_column3)
   where [조건]
 

output 구문을 통해 반환되는 값들을 into 구문을 통해 특정 TABLE 혹은 TEMP TABLE 에 해당 행을 삽입한다.

타겟이 되는 TABLE_A, 반환 값을 삽입하는 TABLE_B의 작업이 원자성을 가지어, 하나의 트랜잭션으로 실행된다.

 

TABLE_A의 작업이 실패하거나, TABLE_B의 삽입이 실패할 경우 이 트랜잭션이 원자성으로 인해 rollback처리가 된다.

 

이렇게 하나의 트랜잭션으로 묶어 두개의 테이블에 동시에 작업을 진행 할 수 있는데,

update문을 구성할 때 output, into 구문을 사용할 경우 아래와 같은 작업에 효과적으로 사용할수 있다.

 

특정 컬럼의 변경 전/후를 저장하여 특정 테이블에 로그로 남긴다거나,

컬럼의 전/후 비교값을 계산하여 특정 테이블에 담아두는 등의 작업을 쉽게 진행 할 수 있다.

 

구문의 특징 및 주의사항

output과 into 구문을 사용 할 경우, 여러 가지 특징 들이 있다.

 

output 구문에는 서브쿼리가 들어갈 수 없다. / 변수 값을 사용 할 수 있다.

into 구문의 TABLE_B에 output 구문으로 반환되는 값과

다른 테이블의 값을 동시에 넣고 싶어 서브쿼리를 사용할 경우 에러가 발생한다.

위와 같은 경우, TABLE_C의 값을 변수에 담아 변수를 활용하여 output 절을 사용 함으로 해결한다.

변수를 선언 하여 해당 변수에 값을 사용한다.

 

into 구문의 TABLE에는 트리거가 있으면 안된다.

TABLE_B에 트리거를 생성한 상태이다.

트리거가 설정되어있는 테이블을 into 구문의 TABLE로 사용할 경우 아래와 같은 에러가 발생한다.

 

Posted by 요지
,

 

 

출처 : https://bigenergy.tistory.com/entry/MSSQL-Trigger-%ED%8A%B8%EB%A6%AC%EA%B1%B0-%EC%82%AC%EC%9A%A9%EB%B0%A9%EB%B2%95%EC%9D%84-%EC%95%8C%EC%95%84%EB%B3%B4%EC%9E%90

 

MSSQL Trigger, 트리거 사용방법을 알아보자

오늘은 MSSQL Database의 트리거 사용방법을 알아보도록 하겠습니다. 트리거는 무엇에 쓰는 물건인고?? 테이블에 Insert, Update, Delete 등의 데이터 변화가 생길경우 이 이벤트를 감지해 주는 역할을 하

bigenergy.tistory.com

 

 

 

 

오늘은 MSSQL Database의 트리거 사용방법을 알아보도록 하겠습니다.

 

트리거는 무엇에 쓰는 물건인고??

테이블에 Insert, Update, Delete 등의 데이터 변화가 생길경우
이 이벤트를 감지해 주는 역할을 하는것이 트리거입니다.
즉, 삽입, 수정, 삭제등의 데이터조작이 발생할 경우 이를 감지하여 이를 어떠한 특수목적을 가지고
부가적인 처리를 할 수 있다는 것입니다.

 

 

MSSQL Trigger 사용방법

 

 

어떤때에 트리거를 사용하나??

트리거는 다양한 용도로 사용할 수 있는데 보통은 데이터의 입력,수정,삭제등의 이벤트에 따른 로그기록, 
예를 들면 언제 지워졌고, 지워진 데이터가 무엇인지를 로그 테이블에 기록하거나 
특정테이블에 데이터 변화가 감지되면 다른테이블의 데이터도 조작을 해야하는 경우, 
그리고 데이터가 테이블에 기록되기전에 데이터 무결성 체크와 유효성 체크등이 필요한 경우 등에 사용될 수 있습니다.

 

MSSQL Trigger를 이용한 로그테이블 사용해 보기


테스트를 위해 아래와 같이 2개의 테이블을 만들었습니다.
TestA 테이블에 데이터를 인서트,업데이트,딜리트 할 것이고,  TestB 테이블에는 트리거를 이용한 로그를 남길 것입니다.

 

 

트리거 테스트를 위한 샘플 테이블 생성

 

 

CREATE TABLE [dbo].[TestA](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[userSerialNo] [varchar](10) NOT NULL,
	[userName] [varchar](20) NOT NULL,
	[userOld] [int] NULL,
	[userPhone] [varchar](20) NULL,
	[userMemo] [varchar](100) NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[TestB](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[userSerialNo] [varchar](10) NOT NULL,
	[userName] [varchar](20) NOT NULL,
	[userOld] [int] NULL,
	[userPhone] [varchar](20) NULL,
	[userMemo] [varchar](100) NULL,
	[trgType] [varchar](1) NOT NULL
) ON [PRIMARY]
GO

 

테이블을 모두 만들었으면 트리거도 만들어 줘야 합니다.
SSMS 에서 손쉽게 마우스를 클릭해서 새 트리거 메뉴를 이용해 만들어 줍니다.
트리거는 TestA 테이블에 만들어줍니다.

 

 

 

 

MSSQL Trigger 생성

 

 

아래와 같은 기본 구문이 자동으로 생성되는데 트리거 명칭과 트리거가 적용될 테이블명을  지정해주면 됩니다.

-- SSMS 기본 생성 트리거 SQL 구문 
-- 
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>  
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>  
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE> 
AS  
BEGIN 
-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
SET NOCOUNT ON; 

    -- Insert statements for trigger here 

END 
GO 

 

트리거 생성시 주의사항

테이블에 인서트가 됐을 경우 inserted 라는 테이블을 통해 인서트된 데이터에 접근이 가능합니다. 또한, delete의 경우 deleted 테이블에 접근해 삭제된 데이터에 접근이 가능하며, updated 라는 테이블은 없는데 이는 SQL UPDATE의 개념이 삭제 후 업데이트 이기 때문에 inserted 를 참조하면됩니다.

 

실제 생성할 트리거의 쿼리문입니다.

 

-- 테스트용 생성 트리거
--
CREATE TRIGGER dbo.TRG_TESTA_IUD_TEST
   ON  dbo.TestA 
   AFTER INSERT,DELETE,UPDATE --트리거 수행이 인서트,딜리트,업데이트 이후에 동작한다는 뜻입니다.
AS 
BEGIN

	SET NOCOUNT ON;

	DECLARE @Action As CHAR(1) -- 인서트인지 업데이트인지 삭제인지 구분할 변수

	SET @Action = 'I' --기본 인서트 플래그

	-- deleted 테이블에 데이터가 있고 inserted에도 데이터가 있으면 UPDATE 된것으로 판단
	-- deleted 테이블에 데이터가 없으면 인서트로 판단
	
	
	IF Exists(select * from deleted)
	begin
		SET @Action = (Case when Exists(select * from inserted) then 'U' else 'D' End)
	end


	Declare @userSerialNo As Varchar(10)
	Declare @userName As Varchar(20)
	Declare @userOld As Int
	Declare @userPhone As Varchar(20)
	Declare @userMemo As Varchar(100)


	-- insert, update, delete를 구분하여 해당 테이블에서 데이터를 가져와 변수에 할당
	
	IF @Action = 'D'
	Begin
		select @userSerialNo=userSerialNo, @userName=userName, @userOld=userOld,
		       @userPhone=userPhone, @userMemo=userMemo
	      from deleted --삭제된 테이블의 알리아스라고 생각하면됨
	End
	Else 
		Begin
		select @userSerialNo=userSerialNo, @userName=userName, @userOld=userOld,
		       @userPhone=userPhone, @userMemo=userMemo
	      from inserted --인서트 또는 삭제된 테이블의 알리아스라고 생각하면됨
		End


	-- 로그테이블인 TestB 에 기록
	
	Insert into TestB (userSerialNo, userName, userOld, userPhone, userMemo, trgType)
	            values (@userSerialNo, @userName, @userOld, @userPhone, @userMemo, @Action)
END
GO

 

트리거가 생성되었으니 TestA 테이블에 인서트, 업데이트, 삭제 등의 데이터 조작을 해보고
정상적인 트리거 동작을 했는지 TestB 테이블을 조회하여 데이터를 검색해 봅니다.

 

 

트리거를 이용한 이력 생성

 

 

TestB.trgType 필드에 I, U, D 등의 플래그가 표시되는 것을 확인 할 수 있습니다.
MSSQL 트리거 사용법을 이해하는데 도움이 되셨길 바랍니다.

출처: https://bigenergy.tistory.com/entry/MSSQL-Trigger-트리거-사용방법을-알아보자 [빅에너지™:티스토리]

Posted by 요지
,

 

 

출처 : https://hongik-prsn.tistory.com/78

 

MSSQL 계층형 쿼리, 트리구조로 실적 구하기(WITH, 재귀 CTE)

1. 트리구조 테이블로 만들기 이것은 한 회사의 부서 조직도를 나타낸 것이다 이것을 테이블로 표현하면 아래와 같이 표현될 것이다. CREATE TABLE DEPT_TABLE ( DEPT_CD VARCHAR(4), PARENT_DEPT_CD VARCHAR(4), DEPT_N

hongik-prsn.tistory.com

 

 

 

 

 

1. 트리구조 테이블로 만들기

예제로 사용할 데이터의 트리구조

이것은 한 회사의 부서 조직도를 나타낸 것이다 

 

이것을 테이블로 표현하면 아래와 같이 표현될 것이다. 

CREATE TABLE DEPT_TABLE (
    DEPT_CD           VARCHAR(4),
    PARENT_DEPT_CD    VARCHAR(4),
    DEPT_NM           VARCHAR(20)
)

INSERT INTO DEPT_TABLE VALUES ('0000', '', '전체')
INSERT INTO DEPT_TABLE VALUES ('1000', '0000', '임원실')
INSERT INTO DEPT_TABLE VALUES ('1001', '1000', '회장실')
INSERT INTO DEPT_TABLE VALUES ('1002', '1000', '사장실')
INSERT INTO DEPT_TABLE VALUES ('2000', '0000', '전략기획팀')
INSERT INTO DEPT_TABLE VALUES ('2001', '2000', '심사')
INSERT INTO DEPT_TABLE VALUES ('2010', '2000', '마케팅')
INSERT INTO DEPT_TABLE VALUES ('2011', '2010', '온라인마케팅')
INSERT INTO DEPT_TABLE VALUES ('2012', '2010', '오프라인마케팅')
INSERT INTO DEPT_TABLE VALUES ('3000', '0000', 'IT')
INSERT INTO DEPT_TABLE VALUES ('3010', '3000', '정보보안')
INSERT INTO DEPT_TABLE VALUES ('3020', '3000', 'IT혁신')
INSERT INTO DEPT_TABLE VALUES ('3021', '3020', '개발1팀')
INSERT INTO DEPT_TABLE VALUES ('3022', '3020', '개발2팀')

부서 테이블(DEPT_TABLE)은 부서코드(DEPT_CD)와 상위부서코드(PARENT_DEPT_CD)를 갖는다. 

 


2. 계층형 쿼리로 만들기(WITH 절)

오라클에서는 CONNECT BY, START WITH과 같은 내장함수로 간단하게 트리구조를 만들 수 있지만, MSSQL은 WITH 절에서 재귀적 CTE를 구현해야 계층형 쿼리를 만들 수 있다.

 

WITH 절에서 재귀적 CTE를 구현하는 형식은 아래와 같다.

WITH CTE_테이블명(열이름1, 열이름2, 열이름3 ...)
AS
(
    <쿼리문1 : SELECT * FROM TABLE_A>
    UNION ALL
    <쿼리문2 : SELECT * FROM TABLE_A JOIN CTE_테이블명>
)
SELECT * FROM CTE_테이블명;

위의 구문에서 쿼리문1을 앵커 멤버(Anchor Member, AC)라 부르고 쿼리문2를 재귀 멤버(Recursive Member, RM)라 부른다.

 

이를 적용해 보면, 동작 원리는 아래와 같다.

WITH DEPT_CTE AS (
    SELECT DEPT_CD, DEPT_NM, PARENT_DEPT_CD
         , 0 AS LEVEL
         , CONVERT(VARCHAR(500), DEPT_CD) AS CD_PATH
         , CONVERT(VARCHAR(500), DEPT_NM) AS NM_PATH
    FROM DEPT_TABLE WITH(NOLOCK)
    WHERE DEPT_CD = '0000'

    UNION ALL

    SELECT DT.DEPT_CD, DT.DEPT_NM, DT.PARENT_DEPT_CD
         , DC.LEVEL + 1
         , CONVERT(VARCHAR(500), DC.CD_PATH + '>' + DT.DEPT_CD) AS CD_PATH
         , CONVERT(VARCHAR(500), DC.NM_PATH + '>' + DT.DEPT_NM) AS NM_PATH
    FROM DEPT_TABLE DT WITH(NOLOCK)
    INNER JOIN DEPT_CTE DC
            ON DC.DEPT_CD = DT.PARENT_DEPT_CD
)
SELECT replicate('		', LEVEL) + DEPT_NM AS 부서명
     , * 
INTO #DEPT_CTE
FROM DEPT_CTE WITH(NOLOCK)
ORDER BY CD_PATH

1. 최초 호출 시 쿼리문1(앵커 멤버)을 실행, 부서이름이 '전체'인 루트노드를 DEPT_CTE라는 테이블을 최초 생성한다. 레벨은 0으로 초기화

 

2. 쿼리문2(재귀 멤버)를 실행, 레벨 +1 증가, 최초 생성된 DEPT_CTE 테이블의 부서코드와 부서 테이블의 부모코드로 조인해서 루트노드(0000)가 부모노드인 노드들을 가져온다.

 

3.  만약 SELECT 결과가 빈 값이 아니라면 쿼리문2(재귀 멤버)를 다시 재귀 호출. 2번 과정 반복, 부서테이블의 부모노드가 임원실(1000), 전략기획팀(2000), IT(3000)인 노드들을 가져온다. 레벨은 +1 증가

 

3. SELECT 결과가 빈값이면 재귀 호출 중단

 

4. 외부의 SELECT 문을 실행해서 앞 단계에서의 누적 결과를 UNION ALL을 통해 가져옴.

레벨 값과 REPLICATE함수를 이용해주면 보다 더 한눈에 표현이 가능하게할 수 있다.

 


3. 각 부서별 실적을 집계해보자.

실적은 리프노드 부서들만 넣어서 테이블을 만들어 준다.

CREATE TABLE DEPT_PERFORMANCE (
    DEPT_CD    VARCHAR(4),
    DEPT_AMT   BIGINT
)

INSERT INTO DEPT_PERFORMANCE VALUES ('1001', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('1002', '2000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2001', '3000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2011', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('2012', '2000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3010', '3000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3021', '1000')
INSERT INTO DEPT_PERFORMANCE VALUES ('3022', '2000')
SELECT DEPT_AMT, A.*
FROM #DEPT_CTE A WITH(NOLOCK)
OUTER APPLY (
    SELECT SUM(DEPT_AMT) DEPT_AMT
    FROM DEPT_PERFORMANCE B WITH(NOLOCK)
    INNER JOIN #DEPT_CTE C WITH(NOLOCK)
            ON C.DEPT_CD = B.DEPT_CD
           AND C.CD_PATH LIKE A.CD_PATH+'%'
) DP
ORDER BY CD_PATH

1. DEPT_CTE 테이블에 실적 테이블(DEPT_PERFORMANCE)을 조인해 준다.

 

2. CD_PATH와 LIKE를 조건으로 DEPT_CTE 테이블을 다시 조인해서 SUM을 해준다. 1번 이미지와 2번 이미지의 NM_PATH를 보면 이해하기 쉬울 것이다.

DEPT_CTE 테이블을 다시 조인하고 SUM을 하기 전 결과값

 

3. DEPT_AMT를 보면 각 계층별 부서별 합계가 구해진 것을 볼 수 있다.

 

 

 

Posted by 요지
,

 

mssql 쿼리문에 빨간 줄이 발생하는 경우

해결하려면 상단 표시줄에 편집(E)->IntelliSense(I)->로컬 캐시 새로 고침(R)

 

 

 

 단축키로는 Ctrl + Shift + R을 동시에 눌러줍니다.

 

 

 

 

Posted by 요지
,

 

출처 : https://gent.tistory.com/348

 

[MSSQL] EOMONTH 함수, 월의 마지막 날짜 구하기 (LAST_DAY)

SQL Server 2012 버전부터 월의 마지막 일자를 구할 수 있는 EOMONTH() 함수가 추가되었다. 오라클 SQL의 LAST_DAY와 동일한 기능을 수행하는 함수이다. 월의 마지막 날짜 구하기 (SQL Server 2012 이상) SELECT EOM

gent.tistory.com

 

 

 

 

 

SQL Server 2012 버전부터 월의 마지막 일자를 구할 수 있는 EOMONTH() 함수가 추가되었다. 오라클 SQL의 LAST_DAY와 동일한 기능을 수행하는 함수이다.

 

월의 마지막 날짜 구하기 (SQL Server 2012 이상)

SELECT EOMONTH('2020-09-09') AS result

 

SQL Server 2012 버전부터 EOMONTH (End of Month) 함수를 사용하여 간편하게 해당 일자 기준으로 해당 월의 마지막 날짜를 구할 수 있다.

 

 

 

Posted by 요지
,