품목코드 일괄변경 작업을 위해 SP를 만들어 보았습니다.
방법은 품목코드 Column(ITEM_CODE)을 가지고 있는 테이블 LIST를 가져온 뒤
해당 테이블 목록을 Fetch Next로 돌리면서
기존 품목코드를 신규 품목 코드로 변경하는 내용입니다.
일단 변수 선언부분 입니다.
-- 변수 설정 --
DECLARE @FullTableName nvarchar(128)
/* 커서 오픈으로 가져오는 select 값 저장 변수 */
, @TableName nvarchar(128), @FieldName nvarchar(9)
/* Query 수행문 저장 변수 */
, @strSQL1 nvarchar(4000), @strSQL2 nvarchar(4000), @strSQL3 nvarchar(4000), @curSQL nvarchar(4000)
/* 테스트를 위한 카운트 값 */
, @i int
ITEM_CODE 컬럼이 존재하는 테이블 목록은 커서를 선언하고
FETCH NEXT FROM 구문을 이용해 총 2개의 컬럼을 select하여 가져옵니다.
1. 테이블명 nvarchar(128)
2. 컬럼명 nvarchar(9) <-- 사실 이 항목은 쓰지 않지만 있으나 없으나 오류는 똑같은 상황입니다.
커서 선언 및 FETCH NEXT 구문은 다음과 같습니다.
-- 커서 선언 : ITEM_CODE 컬럼을 가진 모든 테이블 목록 만큼 반복 --
Set @curSQL = 'DECLARE CUR_TBL CURSOR FOR '
+ 'Select CAST(RTRIM(b.name) as nvarchar(128)) as TableName, CAST(RTRIM(a.name) as nvarchar(8)) as FieldName '
+ ' From ' + @dbName + '..syscolumns a with(nolock), ' + @dbName + '..sysobjects b with(nolock) '
+ ' Where a.id = b.id and UPPER(a.name) like ''ITEM_CODE'' and b.type = ''U'' and a.length < 512 '
+ ' ORDER BY B.name ASC';
EXEC sp_executesql @curSQL;
-- 커서를 열고 --
OPEN CUR_TBL
-- 첫행(테이블명)을 가져온다
FETCH NEXT FROM CUR_TBL INTO @TableName, @FieldName
그리고 WHILE문으로 돌려 줍니다.
While @@FETCH_STATUS = 0
BEGIN
-- 기존 품목코드 레코드를 뽑아서 신규 품목코드로 UPDATE 처리 시작 --
...
...
...
-- 기존 품목코드 레코드를 뽑아서 신규 품목코드로 UPDATE 처리 시작 --
여기까지 다 잘 처리 되었습니다. (테스트 결과 이상 없었습니다.)
그리고 마지막으로 다음 테이블 정보를 가져오기 위해 아래 구문이 수행됩니다.
FETCH NEXT FROM CUR_TBL INTO @TableName, @FieldName
END
그런데 쿼리 수행 결과를 보면
어떤 테이블은 오류 없이 품목코드 변환이 정상적으로 처리 되고
어떤 테이블은 아래와 같은 오류를 나타냅니다.
----- 오류 내용 -----
서버: 메시지 8152, 수준 16, 상태 4, 줄 1
문자열이나 이진 데이터는 잘립니다.
문이 종료되었습니다.
본 오류는 WHILE문 마지막에 들어있는
FETCH NEXT FROM CUR_TBL INTO @TableName, @FieldName 쿼리문이 수행되는 시점에서 발생하며
테이블명이나 컬럼명의 길이 값과 데이터 타입이 문제일 거라 생각하여
다음과 같은 수정을 하였습니다.
1. 데이터 길이 늘리기
데이터 길이는 현재 다음과 같이 되어 있습니다.
@TableName nvarchar(128)
@FieldName nvarchar(9)
각각의 변수를 256, 32 등으로 늘려 보았지만 문제가 해결되지 않았습니다.
커서를 선언하고 테이블명과 컬럼명을 가져와보면 테이블명이 아무리 길어봐야 25글자 이내입니다.
컬럼명은 'ITEM_CODE' 로 9글자 입니다.
2. 데이터 타입 맞추기
데이터 타입의 문제인가 싶어서 테이블명, 컬럼명 정보를 가져오는 커서 선언 부분에서
CAST를 이용해 nvarchar(128)과 nvarchar(9) 로 데이터 타입을 변환시켜 보았습니다.
하지만 문제는 여전히 해결되지 않고 같은 오류를 계속해서 냅니다.
정상 처리된 테이블명 중 하나를 뽑자면 '_INTRA_SHOPITEM_LIST' 입니다.
오류 처리된 테이블명 중 하나를 뽑자면 '_temp_carpentryPrice_data' 입니다.
두 테이블명 모두 문자길이가 25를 넘지 않습니다.
데이터 타입을 조정해주고 길이를 늘려 주었는데도 어떤 테이블은 정상 처리되고
어떤 테이블은 '문자열이나 이진 데이터는 잘립니다' 라는 오류를 내는 이유가 뭘까요?
벌써 반나절 이상을 이거땜에 끙끙 싸메고 있네요..
도움 부탁드립니다.
Comment 8
-
처리짱
2013.07.09 15:20
-
래리
2013.07.09 15:43
위 구문에서 커서 부분만 소스 따로 보여드린거구요.
전체 소스는 아닙니다.
-
래리
2013.07.09 15:46
혹시 필요하시면 전체소스 입니다.
CREATE procedure [dbo].[sp_ex_code]
@dbName nchar(8) -- 코드변경 적용할 DB명
AS
Begin
Set NOCOUNT ON
-- 변수 설정 --
DECLARE @FullTableName nvarchar(128) /* dbName + TableName 저장 변수 */
, @TableName nvarchar(128), @FieldName nvarchar(8) /* ITEM_CODE 컬럼이 존재하는 테이블 정보 */
, @strSQL1 nvarchar(4000), @strSQL2 nvarchar(4000), @strSQL3 nvarchar(4000) /* Query 수행문 저장 변수 */
, @curSQL nvarchar(4000) /* 커서에서 동적쿼리 사용을 위한 변수 */
, @i int
-- 코드변환 업데이트 전/후 비교 테이블 별 이전 레코드 개수 변화 --
CREATE TABLE #IMSI_TBL (TBL_NAME nvarchar(32), OLD_CODE nchar(13), OLD_CNT1 int, OLD_CNT2 int, NEW_CODE nchar(13), NEW_CNT1 int, NEW_CNT2 int)
Set @i = 0
-- 커서 선언 : ITEM_CODE 컬럼을 가진 모든 테이블 목록 만큼 반복 --
Set @curSQL = 'DECLARE CUR_TBL CURSOR FOR '
+ ' Select CAST(RTRIM(b.name) as nvarchar(128)) as TableName, CAST(RTRIM(a.name) as nvarchar(8)) as FieldName '
+ ' From ' + @dbName + '..syscolumns a with(nolock), ' + @dbName + '..sysobjects b with(nolock) '
+ ' Where a.id = b.id and UPPER(a.name) like ''ITEM_CODE'' and b.type = ''U'' and a.length < 512 '
+ ' ORDER BY B.name ASC';
EXEC sp_executesql @curSQL;
-- 커서를 열고 --
OPEN CUR_TBL
-- 첫행(테이블명)을 가져온다
FETCH NEXT FROM CUR_TBL INTO @TableName, @FieldName
-- 마지막 행까지 반복 (테이블 개수 만큼 LOOP / 테이블명, 기존품목코드 레코드수, 변경된 품목코드 레코드수 저장 / 업데이트)
While @@FETCH_STATUS = 0
BEGIN
-- 카운트용 (사용안함) --
Set @i = @i+1
-- 지정된 DB내에서 전체 테이블 개체명 지정
Set @FullTableName = @dbName + '..' + @TableName;
-- 변경 전 테이블 내에 존재하는 OLD ITEM CODE COUNT와 NEW ITEM CODE COUNT 현황 INSERT --
Set @strSQL1 = N'INSERT INTO #IMSI_TBL '
+ 'select ''' + @FullTableName + ''' TBL_NAME, A.OLD_CODE1, ISNULL(B.OCNT1,0) OLD_CNT1, 0 as OLD_CNT2, A.NEW_CODE1, ISNULL(C.NCNT1,0) NEW_CNT1, 0 as NEW_CNT2 from '
+ '(select OLD_CODE1, NEW_CODE1 from (select ITEM_CODE OLD_CODE1, NEW_CODE NEW_CODE1 from ' + @dbName
+ '..ITEM_MASTER_CTR) CTR) A Left Join '
+ '(select ITEM_CODE, COUNT(ITEM_CODE) OCNT1 from ' + @FullTableName + ' where ITEM_CODE in (select ITEM_CODE from ' + @dbName + '..ITEM_MASTER_CTR) Group By ITEM_CODE) B On B.ITEM_CODE = A.OLD_CODE1 Left Join '
+ '(select ITEM_CODE, COUNT(ITEM_CODE) NCNT1 from ' + @FullTableName + ' where ITEM_CODE in (select NEW_CODE from ' + @dbName + '..ITEM_MASTER_CTR) Group By ITEM_CODE) C On C.ITEM_CODE = A.NEW_CODE1 '
EXECUTE sp_executesql @strSQL1;
-- 신규 품목코드로 업데이트 하고..
Set @strSQL2 = N'update ' + @FullTableName + ' set ITEM_CODE = CTR.NEW_CODE from '
+ @dbName +'..ITEM_MASTER_CTR CTR where ' + @FullTableName + '.ITEM_CODE = CTR.ITEM_CODE';
EXECUTE sp_executesql @strSQL2;
-- 변경 후 테이블 내에 존재하는 OLD ITEM CODE COUNT와 NEW ITEM CODE COUNT 현황 UPDATE --
Set @strSQL3 = N'UPDATE #IMSI_TBL SET '
+ 'OLD_CNT2 = RST.OLD_CNT2, NEW_CNT2 = RST.NEW_CNT2 from ('
+ 'select ''' + @FullTableName + ''' TBL_NAME, A.OLD_CODE, ISNULL(B.OCNT2,0) OLD_CNT2, A.NEW_CODE, ISNULL(C.NCNT2,0) NEW_CNT2 from '
+ ' (select OLD_CODE, NEW_CODE from (select ITEM_CODE OLD_CODE, NEW_CODE NEW_CODE from ' + @dbName + '..ITEM_MASTER_CTR) CTR) A Left Join '
+ ' (select ITEM_CODE, COUNT(ITEM_CODE) OCNT2 from ' + @FullTableName + ' where ITEM_CODE in (select ITEM_CODE from ' + @dbName + '..ITEM_MASTER_CTR) Group By ITEM_CODE) B On B.ITEM_CODE = A.OLD_CODE Left Join '
+ ' (select ITEM_CODE, COUNT(ITEM_CODE) NCNT2 from ' + @FullTableName + ' where ITEM_CODE in (select NEW_CODE from ' + @dbName + '..ITEM_MASTER_CTR) Group By ITEM_CODE) C On C.ITEM_CODE = A.NEW_CODE) RST '
+ 'where #IMSI_TBL.TBL_NAME = ''' + @FullTableName + ''' and #IMSI_TBL.OLD_CODE = RST.OLD_CODE and #IMSI_TBL.NEW_CODE = RST.NEW_CODE '
EXECUTE sp_executesql @strSQL3;
-- 다음 테이블로 이동
FETCH NEXT FROM CUR_TBL INTO @TableName, @FieldName
END
-- 커서를 닫고 종료
CLOSE CUR_TBL
DEALLOCATE CUR_TBL
-- 수행결과물 저장 테이블 최종 호출 --
Select * from #IMSI_TBL
END
GO
-
처리짱
2013.07.09 15:56
맨위에
SET NOCOUNT ON 다음부터
마지막에
Select * from #IMSI_TBL
END
요기까지 전체를 변수에다가 집어넣고 exec 를 하셔야 정상 동작 할거 같으네요.
-
래리
2013.07.09 17:41
아.. 답변 감사드립니다~
일단 한번 해보겠습니다.
그런데 통채로 변수에 담아서 처리를 해야 정상 작동하는 이유를 알 수 있을까요?
그리고 내용이 너무 많아서 통채로 변수에 넣기 힘들거 같은데 상관 없을까요?
-
래리
2013.07.09 18:09
ㅎㅎ 드뎌 찾았습니다.
처리짱님 말씀대로 하려고 무작정 변수에 넣는 작업을 시도하다가
잘못된 이유를 찾았네요...
문제는 임시테이블 #IMSI_TBL 에서 테이블명 컬럼 사이즈를 nvarchar(32)로 잡았던게 문제였습니다.
테이블 이름은 길어봐야 25자였지만 그 앞에 DB명을 붙여주고 있었는데 그게 붙으면 32자가 넘는 테이블명이
생기더라구요..
그래서 임시테이블 생성에서 테이블명 컬럼을 nvarchar(128)로 잡아주고 다시 수행했더니 잘되네요 ㅎㅎ
변수쪽 문제인줄 알고 계속 변수만 수정했는데 SQL은 에러 라인 찾기가 너무 힘드네요...
-
처리짱
2013.07.09 18:55
음.. 위에 구문이 동작하는군요..
DECLARE CUR_TBL CURSOR FOR 이거는 변수 안에 있고
밑에서 exec해도 OPEN CUR_TBL 이때 인식을 못할줄 았았는데요..
-
래리
2013.07.10 09:10
동적쿼리 적용을 위해 여기 저기 사용된 소스를 보면서 제 상황에 맞게 수정하다 보니 만들어 지더라구요..
커서 선언부를 변수에 할당 후 EXEC sp_executesql 로 실행해주면 작동 하더라구요..
위 구문이 실행이 되나요??
@curSQL 요거 안에 전체 구문을 넣고
마지막에 EXEC sp_executesql @curSQL 를 하셔야 할거 같은데요