개발자생각

일반적인 update 문

update 테이블명

set 

컬럼1 = 값

, 컬럼2 = 값

where 컬럼=조건

다른 테이블 조건에 따른 update 문

update 테이블명

set 

컬럼1 = 값

, 컬럼2 = 값

from 테이블명2 Tb2

where 테이블명.컬럼=tb2.조건

조건절에 다른테이블들과 조인이 있는 update 문

update 테이블명

set 

컬럼1 = 값

, 컬럼2 = 값

from 테이블명2 Tb2

inner join 테이블명3 tb3

on tb2.컬럼 = tb3.컬럼

where

테이블명.컬럼=tb2.컬럼

and 테이블명.컬럼=tb3.컬럼

일반적으로 스케쥴이나 타DB 연동, 대량데이터 insert 작업을 수행하는 경우 기존 자료와 중복체크 후 작업해야 하는 경우가 꽤 있습니다.

보통은 해당 자료에 대한 select 작업으로 중복을 체크한 후 중복이 없을 경우 insert 하고 중복이 있는 경우 update 작업을 합니다.

이 작업이 비효율적이라고 판단될 경우 해당 자료와 중복되는 자료를 delete 처리하고 insert 하는 경우도 꽤 있습니다.

이런 작업에 대해 가장 적합한 쿼리는 제 생각에는 merge 문의 사용입니다.

merge 문은 조건(키)에 해당하는 자료가 존재할경우 update 처리를 존재하지 않을 경우 insert 처리를 해주는 쿼리로 한문장으로 모든 작업을 처리 합니다.

물론 대용량(10만건이상? 100만건이상?) 작업을 진행할 경우 merge문은 CPU점유율이 매우 올라가고 작업속도도 매우 안좋은 단점이 있지만 중소규모 작업(10만건 이하)에서는 매우 유용한 쿼리 입니다.

MSSQL에서 권한 관련 내용은 잘 생각하지 않는 경우가 많지만 select, insert, update, delete 권한 모두 필요합니다.

쿼리형식

declare @USER_ID varchar(20)  ,@USER_CD varchar(20) 

set @USER_ID = 'aaaa' 
set @USER_CD = 'bbbb' 

MERGE MY_USER as TB1 
   USING (VALUES (@USER_ID, @USER_CD)) as tb2(USER_ID,USER_CD) 
     ON tb1.USER_ID = tb2.USER_ID
           and tb1.USER_CD = tb2.USER_CD 
   WHEN MATCHED THEN 
      UPDATE SET SERVICE_NO = 'dddd' 
         , USE_YN = 'N' 
         , INS_DATE = getdate()  
      WHEN NOT MATCHED THEN  
         INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) 
         VALUES(@USER_ID, @USER_CD , 'ccccc', 'Y' , getdate());

대략적으로 설명하면

MERGE MY_USER as TB1 
   USING (VALUES (@USER_ID, @USER_CD)) as tb2(USER_ID,USER_CD) 
     ON tb1.USER_ID = tb2.USER_ID
           and tb1.USER_CD = tb2.USER_CD 

MY_USER 이라는 테이블이 TB1

외부에서 입력받은 @USER_ID, @USER_CD 두개의 컬럼으로 구성된 테이블이 TB2 가 됩니다.

이 두 테이블에서 tb1.user_id와 tb2.user_id 가 동일한지 비교합니다. (조건1)

이 두 테이블에서 tb1.user_cd와 tb2.user_cd 가 동일한지 비교합니다. (조건2)

물론 조건은 1개가 되어도 되고 더 많은 조건을 주어되 됩니다.

   WHEN MATCHED THEN 
      UPDATE SET SERVICE_NO = 'dddd' 
         , USE_YN = 'N' 
         , INS_DATE = getdate()  

2개의 조건을 비교한 결과 일치하면 update 문을 실행합니다.

merge 문에서 테이블명을 지정했고 조건을 주었으므로 update 문에는 각 칼럼의 데이터만 지정합니다.

      WHEN NOT MATCHED THEN  
         INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) 
         VALUES(@USER_ID, @USER_CD , 'ccccc', 'Y' , getdate());

만약 조건이 일치하지 않는다면 insert문을 실행해줍니다.

insert 문도 update 문과 마찬가지로 테이블명은 merge문에서 지정을 했기 때문에 isnert 컬럼과 데이터만 지정해줍니다.

java 에서 활용할때는 다음과 같이 사용합니다.

sqlText  = " MERGE MY_USER as TB1  \n"; 
sqlText  += " USING (VALUES (?,?)) as tb2(USER_ID,USER_CD)   \n"; 
sqlText  += " ON tb1.USER_ID = tb2.USER_ID  \n";

sqlText  += "   and tb1.USER_CD = tb2.USER_CD  \n"; 
sqlText  += "  WHEN MATCHED THEN \n"; 
sqlText  += "     UPDATE SET SERVICE_NO = ? \n"; 
sqlText  += "     , USE_YN= ? \n"; 
sqlText  += "     , INS_DATE = getdate() \n"; 
sqlText  += " WHEN NOT MATCHED THEN \n"; 
sqlText  += "  INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) \n"; 
sqlText  += "  VALUES(?, ?, ?, ? , getdate()) ;\n";


PreparedStatement ps = Dbcon.prepareStatement(sqlText); 
ps.setString(1, temp1); 
ps.setString(2, temp2);


ps.setString(3, temp3); 
ps.setString(4, temp4);



ps.setString(5, temp5); 
ps.setString(6, temp6); 
ps.setString(7, temp7); 
ps.setString(8, temp8);


ps.executeUpdate();

일반적으로 MS-SQL 사용자(관리자) 중에 로그를 제대로 활용하는 사람은 많지 않을것이다.

매번 용량만 많이 차지하는 로그...

로그정리 및 사이즈 축소 방법을 소개 하려고 한다.

/* DB 로그파일 축소~~~~  */ 
use [MY_DB];


-- 복구모델을변경 
ALTER DATABASE [MY_DB] SET RECOVERY SIMPLE 
go


-- 로그를잘라낸다. 
DBCC SHRINKFILE(MY_DB_LOG) 
go 
EXEC sp_helpdb [MY_DB] 
go



-- 복구모델을변경 
ALTER DATABASE [MY_DB] SET RECOVERY FULL 
go



-- 로그사이즈 조정
DBCC SHRINKFILE('MY_DB_LOG', 10)  ;

일반적으로 테이블 내의 데이터를 모두 삭제하는 경우 delete 문을 많이 사용한다.

DELETE MY_TABLE;

또는

DELETE MY_TABLE

WHERE 컬럼=조건1;

일반적으로 where 조건이 꼭 필요한 경우나 데이터 건수가 많지 않은 경우 유용하다.

하지만 데이터가 많은 테이블 전체를 삭제하는 경우 delete 문은 속도가 느리고 많은 트렉젝션 로그 및 리소스를 사용하여 불리하다.

이때 사용하는 쿼리가 TRUNCATE TABLE 이다.

TRUNCATE TABLE MY_TABLE;

TRUNCATE TABLE 문은 트랜젝션 로그를 많이 사용하지 않음으로 쿼리의 실행속도가 매우 빠르고 로그량 증가가 매우 적다.

비슷한 예로 DROP TABLE을 사용하기도 하지만 이 경우 DROP 된 테이블을 재 생성해야 하고 index나 기타 테이블 설정사항들도 다시 생성해야 하므로 TRUNCATE TABLE 문이 유리할 때가 많다.

스케쥴이나 자동화된 쿼리에서 insert 또는 update 속도를 높이기 위하여 index를 제거한 후 update/insert 를 수행하는 경우 쿼리가 끝나면 index를 추가하는 쿼리를 추가해주는 경우가 있는데 이때 index가 제거된 후 다시 생성되지 않으면 다음 실행때 index 제거시 없는 index를 제거 시도 하기 때문에 에러가 발생한다.

이 경우에는 drop table을 사용하여 테이블을 삭제 한 후 select into 명령이나 creat table 명령으로 테이블을 재 생성하고 index를 생성하게 되면 해당 에러를 피할 수 있다.

특정 테이블에 데이터를 insert 할 경우 기존 쿼리나 테이블에서 데이터를 조회하여 그 결과를 insert 할때 사용하는 쿼리이다.

select into의 경우 기존 테이블이 없는 경우 새로 테이블을 생성하며 insert 하고, insert into from의 경우 기존 테이블이 존재할때 해당 테이블에 조회결과를 insert 하는 역할을 한다.

[select into의 예 - 기존 테이블을 백업하는 경우나 쿼리 결과를 테이블로 저장하는 경우 유용]

SELECT * INTO MY_TABLE_BACKUP
FROM 
(
SELECT * FROM
MY_TABLE

) TB1

MY_TABLE의 모든 내용(*) 을 조회하여 결과테이블 MY_TABLE_BACKUP 테이블을 생성하며 insert 해준다.

(이때 주의할점은 생성되는 테이블의 컬럼크기는 해당컬럼 값중 최대 길이로 맞춰진다.)

[INSERT INTO FROM 의 예]

INSERT INTO MY_TABLE_BACKUP 
SELECT * FROM
(
SELECT * FROM
MY_TABLE
) TB1

빨간색 쿼리의 경우 MY_TABLE 에서 모든 항목을 조회하여 저장하게 되므로 결국 

SELECT * INTO MY_TABLE_BACKUP 
FROM  
MY_TABLE

와 같은 문장이다. 

괄호안을 쿼리로 작성한 이유는 쿼리 결과로도 데이터를 insert 할수 있음을 보여주기 위함이다.

만약 자동증가값이 존재하는 테이블의 경우 select into나 insert into from을 사용할때 에러가 발생하는데 이 경우 해당 쿼리 앞뒤로 SET IDENTITY_INSERT 문장을 on / off 해주면 된다.

[자동증가값이 있는 테이블의  예]

SET IDENTITY_INSERT MY_TABLE_BACKUP ON ; 

INSERT INTO MY_TABLE_BACKUP 
SELECT * FROM
( 
SELECT * FROM
MY_TABLE
) TB1

SET IDENTITY_INSERT MY_TABLE_BACKUP OFF ;