제2의 비엔지니어 인생관을 꿈꾸며

Posted
Filed under MSSQL
MSSQL 프로그래밍에세  꽤나 많은 부분을 CRUD 구현하는데, 많은 문장을 작성해 나간다.
WITH 가 Select 에 최적화되어 있다면 MERGE 조인된 테이블의 Insert / Update /Delete 를 구현하는데 최적화 되어있다고 보면 좋겠다.

# MERGE 의 기본 문법 및 의미
MERGE  [Target_Table]  AS TT   -- TT 는 [Target_Table] 의 alias
USING  [Source_Table]  AS TS   -- TS 는 [Source_Table]의 alias
ON ( TT.Column1 = TS.Column1 )  

WHEN MATCHED 
          THEN  [Target_Table]에 대한 처리

WHEN NOT MATCHED BY TARGET   AND  TS.RegDate > '2018-01-01'
           THEN 
[Source_Table] 에 있고 , [Target_Table] 에 없으면, [Target_Table] 에  Insert / Update

WHEN 
NOT MATCHED  BY SOURCE  AND  TT.RegDate < '2018-01-01'
          
THEN  [Source_Table] 에 없고,  [Target_Table] 에 있으면,   [Target_Table]  꺼 처리  Delete / Update

OUTPUT   $action , inserted.* , deleted.*  ;   //  로그보기  MERGE문의 종료는  반디스 세미콜론을  해주어야 한다.
(inserted 는 insert / update 둘다의 의미를 가지고 있으므로 , 따로 updated는 없다.)


2개의 연관된 테이블의 데이터가 "NOT MATCHED" 이긴한데 명시적으로 아래와같이 이용때
  BY TARGET   :  Source 에는 있고, Target 에 없으면
  BY SOURCE  :  Source 에는 없고, Target 에 있을때

 주의  :  WHEN MATCHED 를 제외하고  WHEN NOT MATCHED BY TARGET  처리시  WHEN NOT MATCHED  BY SOURCE 조건도 만족한다면 2개처리한다.   (WHEN MATCHED 를 제외)
1개의 WHEN 조건을 처리하고 종료하는 구조가 아님 , TARGET 과 SOURCE를 2개다 처리하게된다.

예를 들어 BY TARGET 처리 후 BY SOURCE 조건도 만족한다면 SOURCE 조건도 처리함 (코드의 순서와 관계없음)




# 테스트 테이블 및 데이터 만들기
-- 테이블 생성
create table #tbl1 (
 idx tinyint ,  name varchar(20) )
create table #tbl2 ( idx tinyint ,  name varchar(20) )


-- 더미 INSERT
insert #tbl1 values( 1 , 'aaa')
insert #tbl1 values( 2 , 'bbb')

insert #tbl2 values( 3 , 'ccc')
insert #tbl2 values( 4 , 'ddd')
insert #tbl2 values( 5 , 'eee')

-- 데이터 확인
select * From #tbl1
go
select * From #tbl2


# 간략하게 MERGE 이용
MERGE #tbl2 As TT -- Target
USING #tbl1 As TS -- Source
ON (TT.idx = TS.idx)
-- Source 에는 있고 Target 에 있으면, Source 참조로 UPDATE
WHEN MATCHED 
THEN UPDATE SET TT.NAME = TS.NAME
WHEN NOT MATCHED -- BY TARGET 생략시  NOT MATCHED BY TARGET  과 동일
THEN INSERT (idx ,name ) values(TS.idx , TS.name)




# BY TARGET  ,BY SOURCE 를 이용하여, 명시적으로처리
MERGE #tbl2 TT -- Target
USING #tbl1 TS -- Source
ON (TT.idx = TS.idx)
-- Source 에는 있고 Target 에 있으면, Source 참조로 UPDATE
WHEN MATCHED 
THEN UPDATE SET TT.NAME = TS.NAME

-- Source 에는 있고 Target 에 없으면, Source 참조로 Target 에INSERT
WHEN NOT MATCHED BY TARGET  -- AND TS.idx = 5  조건 추가 할 수 있음
THEN INSERT (idx ,name ) values(TS.idx , TS.name)

-- Source 에는 없고, Target 에 있으면 Target 에 Delete or Update
WHEN NOT MATCHED BY SOURCE -- AND TT.idx  >= 2 조건 추가 할 수있음
THEN Delete   -- or THEN UPDATESet idx = 1000 
OUTPUT   $action , inserted.* ,   deleted.*;   -- 로그사용

위 쿼리 실행 


[#tbl1 과 #tbl2 원본 Select]



[위의 Merge 실행후 Log]



[최종결과]
빨강네모 -  NOT MATCHED BY TARGET   - Insert
파랑네모 - NOT MATCHED BY SOURCE  - Delete




[최종결과]

빨강네모 -  NOT MATCHED BY TARGET   를 통해 Insert 만하고 종료하지 않고 ,
파랑네모 - NOT MATCHED BY SOURCE  조건도 만족하는것을 감지하는순간 Delete 도 동시에 처리한다.

정리하자면,
일단 Merge ~ Using ~ On 조건을 통해 Not Matched 조건이 발생하면 1개의 Not Matched(BY TARGET) 의 조건만
처리하지 않고, 
처리 후 다른 Not Matched(BY SOURCE) 조건에 만족하면 연속해서 발생한다.
( 위에 2조건을 만족하게되면 MATCHED 도 만족하게 되지만,  MATCHED 는 일어나지 않음 )
2018/04/02 18:27 2018/04/02 18:27
Posted
Filed under MSSQL
생성된 파일그룹 안에 포함된 파일이 삭제가 안될때.,,

 Alter database [partitionDB] remove file [partitionDB_FG5]  -- 파일  삭제시도
파일그룹
'partitionDB_FG5'(비어있지않으므로제거할수없습니다
. -- 오류발생
 

-- DB 축소실행 ,
DBCC SHRINKFILE ('partitionDB_FG5' ,EMPTYFILE);  -- 그룹의  파일명(물리 파일명 아님)
 
-- 삭제 재시도,
Alter database [partitionDB] remove file [partitionDB_FG5]  -- 삭제 완료


삭제완료, 이제  [FG5] 파일그룹에 [partitionDB_FG5] 를 다시 추가해보자
-- DB 축소 명령어 후 partitionDB_FG5 파일을 FG5그룹에 다시 추가하려고하는데, 

Alter DataBase [partitionDB] add FileGroup [FG5]
Alter DataBase [partitionDB] add File  (Name ="partitionDB_FG5", FileName = "물리파일") 
                                                                                                              To FileGroup  [FG5]

Backup log 를 실행하여  .. 어쩌구 저쩌구 ... LSN .... 오류발생

그럴땐, Log 파일 까지 비워줘야 한다. 로그파일 축소 (MSSQL 2008 이전과 이후 버전 방법이 다르다 아래참고)

[ MSSQL 2008 이전 ]
---------------------------------------------------------------------------------------------------------------------------------------------
BACKUP LOG [DB명] WITH TRUNCATE_ONLY -- DB 로그 축소
BACKUP LOG [DB명] WITH NO_LOG -- DB 로그 삭제

[MSSQL 2008 이후]
---------------------------------------------------------------------------------------------------------------------------------------------
ALTER DATABASE
 [partitionDBSET RECOVERY SIMPLE  -- 복구모델 SIMPLE 로변경

DBCC SHRINKFILE([partitionDB_log])  -- 로그 축소 (로그명)
ALTER DATABASE [partitionDBSET RECOVERY FULL -- 복구모델 다시 FULL 로 변경




-- 다시 파일그룹에 추가해보자 
Alter DataBase [partitionDB] add FileGroup [FG5]
Alter DataBase [partitionDB] add File  (Name ="partitionDB_FG5", FileName = "물리파일") 
                                                                                                              To FileGroup  [FG5]

sp_helpfile
go
sp_helpfilegroup

 잘 된다..
 
2018/03/22 17:51 2018/03/22 17:51
Posted
Filed under MSSQL
WITH  [임의의명칭] AS ( 사용하고픈 쿼리 ) 
select * from [WITH 에 서 만들어진 임시테이블 활용]


여러 쿼리 WITH 로 쓸경우 ( 컴마로 구분하여 연속적으로 작성한다 마치 WITH 만 생략된 느낌)
WITH [임시테이블명칭] AS (  ~ 사용하고픈  쿼리 ~ )
, [임시테이블 별칭] AS ( ~ 사용 하고픈 쿼리 ~)  
, [임시테이블 별칭] AS ( ~ 사용 하고픈 쿼리 ~)
select * from [WITH 에 서 만들어진 임시테이블 활용]
select * from [WITH 에 서 만들어진 임시테이블 활용]
→ 여기 (다음) 쿼리에서 활용하면 오류남 (WITH 마지막 쿼리만 유효)


1개의 테이블에서 쿼리된 집합결과를 별칭으로 사용
WITH USER1 AS
(
      select * from t_user where regDate = '2018-01-01'
)
select * from USER1


2개의 쿼리를 연결해서 각각의 별칭의 테이블로 사용하기
WITH USER1 AS
(
          select top 10 * from t_user where RegDate > '2018-01-01' and RegDate < '2018-02-01'
) , USER2 AS
(
          select top 10 * from t_user where RegDate > '2018-02-01' and RegDate < '2018-03-01'
( select * from USER1 )
   union
( select * from USER2 )




2개의 쿼리를 연결해서 각각의 별칭의 테이블로 사용 후 조인
WITH USER1 AS
(
          Select top 10 userid from t_user where RegDate > '2018-01-01' and RegDate < '2018-02-01'
) , USER2 AS 
(
     Select top 10 userid from t_user where RegDate > '2018-02-01' and RegDate < '2018-03-01'
) , USERTOT AS 
(
     (select * from USER1)
         union all
     (select * from USER2)
)
 select  UserName  from t_user A  right outer join USERTOT B on A.UserId = B.UserId 

WITH 로 만들어진 별칭에 대해서는  집합의 개념으로 테이블과 같이 다루어 질수있다.
목적에 따라 다르겟지만, 임시 데이터 활용을 위해 임시테이블(세션,메모리,뷰) 을 더 이상 안만들어도 되겟다.
2018/03/20 17:10 2018/03/20 17:10
Posted
Filed under MSSQL
일반적으로 프로그래밍에서 데이터 validation 이나 값의 체크는 DB 단보다는 유저인터페이스인 
어플단에서 1차적으로 필터링을 한다 하지만 Db 프로그래밍에서의 If 문 또한 자주이용하곤하는데

if 문 대신 case 문을 활용하기도 한다. 뭔가 좀더 간렬함 때문인데 , 아래 코드를보면
아래코드는 @a 의 값에 따라 @b 에 값을 할당하는 예제이다.

declare @a tinyint
declare @b char(3) 

set @b = '000' -- 기본으로  
set @a = 4  -- 일부러 없는 값을 넣음


if @a = 1  
   set @b = 'aaa'

if @b = 2 
  set @b = 'bbb'

if @c = 3
  set @b = 'ccc'

select @b  --  @b는?? 당연히  '000' 출력 너무 당연하다

이제 case when ~ then ~ 문을 살펴보자 

declare @a tinyint
declare @b char(3) 

set @b = '000' -- 기본으로  
set @a = 4  -- 일부러 없는 값을 넣음


set @b= case  

                when @a = 1 then 'aaa' 
                when @a = 2 then 'bbb'
                when @a = 3 then 'ccc'
End

select @b  -- @b 는?? Null 을 출력한다.

안타깝게도 case 문에서는  조건에 만족하는 값이 없으면 null 을 리턴한다.
그럼 난 리턴이 아닌 기본값 '000' 을 받고싶다면 아래와 같이 else 문을 활용하여야한다

set @b= case  
                when @a = 1 then 'aaa' 
                when @a = 2 then 'bbb'
                when @a = 3 then 'ccc'
             else @b   -- else 만 추가, 또는 else '000' 초기값 
End

select @b  -- @b 는 정상적으로 '000' 출력한다.

이번 아티클은 if / case 문법이나 용도를 몰라서라기보다,
case 문을 case 처럼(?) 사용하면 잘쓰는데, if 문 처럼(?) 쓰다보면  행하는 실수를 끄적여봣다.
2018/03/15 13:51 2018/03/15 13:51
Posted
Filed under MSSQL
https://ch1n2.wordpress.com/2009/08/23/ssis-error-code-dts_e_cannotacquireconnectionfromconnectionmanager/

Data Source=localhost;User ID=test;Initial Catalog=DBNAME;Provider=SQLNCLI.1;

Data Source=localhost;User ID=test;Password=test;Initial Catalog=DBNAME;Provider=SQLNCLI.1;
사용자 삽입 이미지

비쥬얼스튜디오에서 작업하고 테스트 통과되고 정상적으로 실행되는걸 확인하고 Package dtsx 파일로 저장하는 단계에서 패스워드가 누락되어 저장되는게 문제였습니다.
사용자 삽입 이미지
패키지 실행 유틸리티에서도 테스트를 진행할려면 패스워드 구문을 넣어줘야 합니다.
사용자 삽입 이미지
작업배치에도 패스워드 구문은 저장이 안되어서 연결문자열에 추가해줘야 합니다.
패키지 실행권한은 sysadmin 권한이 필요하다고 메세지를 뿌려주네요. 사용자권한으로는 실행이 안되는거 같습니다.
이런 문제로 인하여 편집기로 해당부분을 넣어주고 SQL 작업스케쥴에 등록하는데 역시 또한 연결관리자 패스워드를 갖고오지 않았습니다. 배치작업을 수정하여 연결관리자 패스워드 부분에 입력해주면 이 문제는 해결이 됩니다.
32비트 드라이버와 64비트 연결드라이버 이슈로만 알고 있었는데 열어보니 이런 상황이 발생할줄은 생각도 못했습니다. 테스트 정상적으로 다 되었는데 누가 이런데서 이런게 누락되어 저장될줄은 알게 뭡니까...

2018/03/08 19:33 2018/03/08 19:33