배열값을 테이블에 삽입하는 프로시져 > db

출처 : swynk.com

작성자 :

내용 : 배열형으로된 데이터를 Insert 구문으로 바꾸어 저장합니다.

여러 많은 데이터 삽입시 유용 하겠지요.

임시테이블 만들어 커서로 하나씩 삽입할줄 알았는데요..

문자열 처리의 극한으로 하나하나 값을 쪼개내어 루프를 돌며 넣습니다.

무척이나 신경 많이 쓴듯 하네요.

수행방식 :

1 - exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1',
'string1;string2;string3;string4;' , ';' , @pcRtnVal output

이런 구문이 아래와 같은 insert 문으로 변환 됩니다.

will do the following inserts:
Insert into tblTestTable (dcColumn1) values (String1)
Insert into tblTestTable (dcColumn1) values (String2)
Insert into tblTestTable (dcColumn1) values (String3)
Insert into tblTestTable (dcColumn1) values (String4)

 

2 - exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1,dcColumn2',
'string1;string2;string3;string4;' , ';' , @pcRtnVal output

이런 구문이 아래와 같은 insert 문으로 변환 됩니다.

will do the following inserts:
Insert into tblTestTable (dcColumn1, dcColumn2) values
(String1,String2)
and
Insert into tblTestTable (dcColumn1, dcColumn2) values
(String3,String4)

--샘플 테이블 생성
create table test1(
 c1 varchar(10)
 , c2 varchar(10)
)

--프로시져 수행
declare @pcRtnVal varchar(300)
exec usp_UpdateTablewithArray 'test1', 'c1,c2'
, 'asd;asf;asg;ash;' , ';' , @pcRtnVal output

--데이터 조회
select * from test1

 

/*
 Stored Procedure:
 usp_UpdateTablewithArray
 Creation Date:
 27/06/2001
 Written by:
 Matt Tracey
 EMail by:
 matthew.tracey@dataconversion.ie

 Purpose:
 To insert a one dimensional or multi-dimensional array of

 Type Character into a Fields of type Character in a Table

 Input Parameters:
 @pcTableName varchar(50) -
  Table to insert into

 @pcFieldArray varchar(300) -
  Fields in Table to Insert values into

 @pcArray varchar(2000)  -
  Array of Values

 @pcArrayDelimiter char (1) -
  Delimiter for Array Values

 Output Parameters:
 @pcRtnVal varchar (300) output -
  If Empty OK
  Else Holds Error

 Return Status:
 @lnError   -
  Holds Error Number
  0 = OK

 Usage:
 exec usp_UpdateTablewithArray 'tblTestTable', 'dcColumn1,dcColumn2',
 'asd;asf;asg;ash;' , ';' , @pcRtnVal output

 Local Variables:
 declare @lnError int
 -- Return Error Value

 declare @lcArray varchar(2000)
 -- Array to Import

 declare @lcSQLString nvarchar(2000)
 -- Holds SQL Update Statement

 declare @lcNumberofFields int
 -- Number of Fields to Import into

 declare @lcNumberofElements int
 -- Number of Elements being imported

 declare @lcUpdateString varchar(1000)
 -- Holds SQL Update Values String

 declare @lcTempString varchar(100)
 -- Holds an Array Value

 declare @lnCounter int
 -- Used as a Counter to cycle through the number of fields

 Called By:  Nothing Yet

 Calls:    Nothing

*/
CREATE procedure usp_UpdateTablewithArray
(
 @pcTableName varchar(50),
 @pcFieldArray varchar(300),
 @pcArray varchar(2000),
 @pcArrayDelimiter char (1),
 @pcRtnVal varchar (300) output
)
as
 set nocount on

 declare @lnError int
 -- Return Error Value
 declare @lcArray varchar(2000)
 -- Array to Import
 declare @lcSQLString nvarchar(2000)
 -- Holds SQL Update Statement
 declare @lcNumberofFields int
 -- Number of Fields to Import into
 declare @lcNumberofElements int
 -- Number of Elements being imported
 declare @lcUpdateString varchar(1000)
 -- Holds SQL Update Values String
 declare @lcTempString varchar(100)
 -- Holds an Array Value
 declare @lnCounter int
 -- Used as a Counter to cycle through the number of fields

 -- Initialise Error and Return Strings
 set @lnError = 0
 set @pcRtnVal = ''

 -- Copy Parameter Array to Local Array
 set @lcArray = rtrim(ltrim(@pcArray))

 -- Check we have a string of Account Numbers to import
 if @lcArray = ''
  begin
   set @pcRtnVal = 'Input Error: Array to Import.'
   return
  end

 -- Get the number of Fields to Update
 declare @lcTempstr varchar(300)
 set @lcTempstr = @pcFieldArray
 select @lcNumberofFields = ( (len(@lcTempstr)-
  len(replace(@lcTempstr, ',', ''))) / len(',') ) +1
 if @lcNumberofFields = 0
  begin
   set @pcRtnVal = 'Input Error: No Field List.'
   return
  end

 -- Get the number of Elements in the array
 declare @lcTempstr2 varchar(2000)
 set @lcTempstr2 = @pcArray
 select @lcNumberofElements = ( (len(@lcTempstr2)-
 len(replace(@lcTempstr2, @pcArrayDelimiter, '')))
 / len(@pcArrayDelimiter) )
 if @lcNumberofElements%@lcNumberofFields <> 0
  begin
  set @pcRtnVal =
  'Input Error: Number of Elements will not fit in Fields.'
  return
  end

 -- Check that there is a trailing delimiter in the Array, if not Add one
 if right(@lcArray,1) <> @pcArrayDelimiter
  select @lcArray = @lcArray + @pcArrayDelimiter

 -- Parse the @lcArray string into the table @pcTableName
 while charindex(@pcArrayDelimiter, @lcArray) > 0
 begin
 -- Get next Elements for the NumberofFields from the Array
 -- and place in @lcUpdateString
 set @lcUpdateString = ''
 set @lnCounter = 1
 while (@lnCounter <= @lcNumberofFields)
 begin
 select @lcTempString = substring(@lcArray,1,
  charindex(@pcArrayDelimiter, @lcArray)-1)
 select @lcArray = substring(@lcArray,datalength(rtrim(@lcArray)) -
 ((datalength(rtrim(@lcArray)) - datalength(rtrim(@lcTempString)))
  - 2), 2000)
 set @lcUpdateString = @lcUpdateString + char(39) + @lcTempString
  + char(39) + ','
 set @lnCounter = @lnCounter +1
 end

 -- Remove the trailing character of the Updatestring
 set @lcUpdateString = left(@lcUpdateString,len(@lcUpdateString)-1)

 -- Build the SQL Statement
 set @lcSQLString = N'insert into  '+ @pcTableName +
   ' (' + @pcFieldArray + ')' +
   ' values (' + @lcUpdateString + ')'
 -- Execute the SQL Statement and check for an Error
 exec sp_executesql @lcSQLString
 set @lnError = @@error
 if @lnError <> 0
  break
 end

 -- Check for any errors
 if @lnError <> 0
 set @pcRtnVal = 'Database Error: Updating Table with Array.'

 set nocount off

 -- Return Error Code
 return @lnError




========================================================================

아래 소스 중에 CREATE...를 1번째 줄로 했을 때,
82번째 줄에 아래와 같은 내용의 소스가 있습니다.

select @lcArray = substring(@lcArray,datalength(rtrim(@lcArray)) -
  ((datalength(rtrim(@lcArray)) - datalength(rtrim(@lcTempString)))
   - 2), 2000)

위 소스에서 문제되는 부분이 datalength() 입니다.
물론 영어권 국가 문자가 1바이트인 국가에서는 전혀 문제가 되질 않습니다.
datalength() 함수는 바이트수를 리턴합니다. 즉 datalength('korea') = 5, datalength('코리
아') = 6 입니다.
그러나 substring의 경우 length부분은 문자수 혹은 바이트수라고 SQL Server 도움말에는 나와
있지만 실제로 테스트 해 보면, substring('코리아;대한민국', 1, 3) = 코리아 입니다.

복잡하게 설명드렸지만... 결론은 한글의 경우는 잘 안된다는 점이지요.
해결 방법은 간단합니다.
위의 datalength()함수를 len()함수로 교체하시면 해결됩니다.
왠만하신분덜 눈치 채셨겠지만~ len() 역쉬 문자수를 반환합니다.