파일을 읽어서 매핑값을 BULK INSERT

해더테이블에 데이터 입력

SELECT IDENT_CURRENT('해더테이블') 을 통해서 해더 테이블에 입력된 최근 SEQ 값을 가져온다.

INSERT INTO TEST_BULK 

SELECT 1, * FROM OPENROWSET(BULK 'c:/TEMP/TEST/lncm_t109.txt' ,firstrow = 4, FORMATFILE= 'c:/TEMP/TEST/lncm_b109.ctl') a
GO

 

<lncm_b109.ctl 파일>

9.0
8
1 SQLCHAR 0 0 "," 1 FIN_YYMM  "" 
2 SQLCHAR 0 0 "," 2 PE_NO     "" 
3 SQLCHAR 0 0 "," 3 ORG_NO    "" 
4 SQLCHAR 0 0 "," 4 TEAM_CD   "" 
5 SQLCHAR 0 0 "," 5 ECMP_YMD  "" 
6 SQLCHAR 0 0 "," 6 RTM_YMD   "" 
7 SQLCHAR 0 0 "," 7 CRET_YMD  ""
8 SQLCHAR 0 0 "\n" 0 FILLER  ""

 

<lncm_t109.txt>

[HEADER]
l
m,
200709,0009705001,300914,01,20020301,20050429,20071006,
200709,0009705002,300914,01,20020301,20030731,20071006,
200709,0009705003,300914,01,20020301,20020731,20071006,
200709,0009705004,300948,01,20020301,20030731,20071006,
200709,0009705005,300914,01,20020301,20030731,20071006,
200709,0009705007,300914,01,20020301,20021130,20071006,
200709,0009705008,300914,01,20020301,20051230,20071006,
200709,0009705009,300914,01,20020301,20020531,20071006,
200709,0009705010,300948,01,20020301,20020930,20071006,