먼저 ADO를 통해 엑셀 데이터 다루기, SQL Server에서 텍스트 파일의 내용 쿼리하기 포스트를 먼저 읽어보는게 좋겠다.
기본적으로 이것과 사용하는 방식은 비슷하니까.
보통 SQL Server에서 외부 데이터를 접근해야 할 경우에는 sp_addlinkedserver를 사용해서 데이터 원본에 해당하는 서버를 추가하곤 하는데, 다른 SQL Server나 Oracle처럼 대형 데이터베이스에 접근할 경우에는 이런 방식이 맞겠지만, 엑셀이나 텍스트 파일의 1회성 데이터 임포트 작업을 SQL 쿼리를 통해 수행할 경우에는 서버를 추가하는 방식이 조금 번거롭다.
이런 경우 다음과 같은 쿼리를 사용해서 엑셀 파일의 데이터에 접근할 수 있다 :
(파일럿 프로젝트에서 쓰인 예제 쿼리이다)
-- 매출 : 팩트 데이터
-- 매출목표 데이터 INSERT
truncate table dbo.f매출목표;
INSERT Into dbo.f매출목표
(년, 월, 부서코드, 거래처코드, 품목그룹코드, 매출목표)
SELECT
*
FROM
OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Documents and Settings\Administrator\바탕 화면\OLAP 자료.xls";
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";')
...[매출목표$];-- 월매출 데이터 INSERT
truncate table dbo.f매출;
INSERT Into dbo.f매출
(날짜ID, 거래처코드, 품목코드, 박스판매수량, 봉판매수량, 판매금액, 박스환입수량, 봉환입수량, 환입금액)
SELECT
*
FROM
OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Documents and Settings\Administrator\바탕 화면\OLAP 자료.xls";
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";')
...['200701$'];
유의해야 할 점은, 시트의 이름 끝에 $ 기호를 붙여서 테이블 이름으로 사용해야 한다는 것. 그리고 아래의 '200701$'가 있겠는데, 시트 이름이 숫자로 시작하거나 시트 이름에 특수문자(공백이라든가, 여튼 일반적이지 않은 문자)가 들어 있을 경우에는 해당 시트 이름을 홑따옴표로 묶어주어야 한다. 다른건 다 맞는데 시트 이름이 틀리다고 나올 경우에는 일단 홑따옴표로 시트 이름을 묶어보는 것도 한 방법.
또한 HDR 속성은 엑셀의 첫번째 행이 컬럼의 이름인지 여부를 나타낸다. 컬럼의 이름이 없을 경우 각 컬럼의 이름은 F1, F2, F3 ... 과 같은 형태로 할당된다.
