The Key's Personal store: mssql 우편번호 쉽게 넣기, 업데이트

관리하는 사이트 중에 우편번호를 DB 에 저장해서 사용하는 곳이 있다.
DB에 저장해서 사용하다 보니 주기적으로 계속 업데이트를 해 줘야 한다.
sql server management studio 프로그램의 데이터 가져오기를 이용해서 넣어 줬다.

우체국 사이트에서 제공하는 데이터가 각 도별 txt 파일이다.
우리나라의 전체 도가 17개인데, ssms 프로그램으로 넣으려면 무려 반복 노가다를 17번을 해야 한다.

bulk insert를 이용해서 한번에 insert 시키도록 하였다.
bulk insert를 하려면 양식(formatfile)이 필요하다.
cmd 창을 열고 아래와 같이 입력해서 뽑아 내자.

C:\Users\Administrator>bcp mydb.dbo.T_ZIPCODE format nul -c -x -f crmZipcode.a.xml -t"|" -T

해당 파일을 바로 사용 하면 좋은데, 우체국 제공 컬럼과 실제 컬럼이 일치 하지 않는 다. 수정이 필요하다.
관리 사이트와 우체국 우편번호 데이터 컬럼의 다른 점은 다음과 같다.
1. 20번 컬럼인 '행정동' 컬럼이 없다.
2. 25번 컬럼인 '구우편번호' 컬럼이 없다.
3. 26번 컬럼인 '우편번호일련번호' 컬럼이 1번째에 있다.

변경하면 다음과 같다.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
 <!--파일의 필드 -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="14" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="160" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="500" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="50" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="400" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="500" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="60" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/> <!-- 행정동 -->
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="100" COLLATION="Korean_Wansung_CI_AS"/> <!--산여부 -->
  <FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="4" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="40" COLLATION="Korean_Wansung_CI_AS"/>
  <FIELD ID="26" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="12"/>
 </RECORD>
 <!--서버의 필드 -->
 <ROW>
  <COLUMN SOURCE="26" NAME="SEQ" xsi:type="SQLINT"/>
  <COLUMN SOURCE="1" NAME="ZIPCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="SIDO" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="ESIDO" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="SIGUNGU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="ESIGUNGU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="EUPMRN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="EEUPMRN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="ROADCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9"  NAME="ROADNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="10" NAME="EROADNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="11" NAME="ZIYN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="12" NAME="GUNBON" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="13" NAME="GUNBU" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="14" NAME="GUNSEQ" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="15" NAME="DABE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="16" NAME="GUNNAME" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="17" NAME="BUBCODE" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="18" NAME="BUBDONG" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="19" NAME="RI" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="21" NAME="SANYN" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="22" NAME="ZIBON" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="23" NAME="EUPSEQ" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="24" NAME="ZIBU" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>
<!-- 서버의 필드에 없는 것은 컬럼을 넣지 않음. -->
<!-- 파일의 필드는 쓰던 쓰지 않던 모두 넣음 -->
<!-- 베이스가 되는 파일 생성 명령어 : C:\Users\Administrator>bcp mydb.dbo.T_ZIPCODE format nul -c -x -f crmZipcode.a.xml -t"|" -T -->

이 양식을 이해 하는 데, 조금 오래 걸렸다.
간단히 설명하면,
1. 상단 RECORD 는 파일의 컬럼이다. 전부 있어야 된다.
2. 하단 ROW는 테이블의 컬럼이다. 수정해서 써야 한다.
내껄 보면 26번이 처음 있고, 20번과 25번이 없는 걸 알 수 있다.
bulk insert 쿼리는 다음과 같다.

BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\강원도.txt'            WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경기도.txt'            WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경상남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\경상북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\광주광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\대구광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\대전광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\부산광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\서울특별시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\세종특별자치시.txt'    WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\울산광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\인천광역시.txt'        WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\전라남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\전라북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\제주특별자치도.txt'    WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\충청남도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )
BULK INSERT mydb.[dbo].[T_ZIPCODE] FROM 'C:\Users\Administrator\Downloads\zipcode_DB\지역별전체 DB\20160504_우편번호_지역별주소\충청북도.txt'          WITH ( FIRSTROW = 2, formatfile='C:\Users\Administrator\crmZipcod.xml' )

FROM 절과 formatfile 의 위치를 수정해서 사용하면 된다.
path를 변수로 만들고 싶었으나, 기본적으로 지원하지 않고, 동적 sql을 사용해야 한다길래 그냥 뒀다.

대략 30분가량의 노가다를 없앴다. :)

The Key's Personal store

2016년 5월 10일 화요일

mssql 우편번호 쉽게 넣기, 업데이트

댓글 없음:

댓글 쓰기

프로필

블로그 보관함