日本郵便謹製のKEN_ALL.CSVをBULK INSERT一発で取り込む

はじめに地雷を撤去しておきましょう

結果論から行くと

use ZIPDB
select code, count(code) as count from dbo.zipcode_master
group by code
having count(code) > 1
order by count(code) desc

みたいなクエリを実行してみたら、重複レコードがある郵便番号が1525件も居やがります。orz
なのでまちがっても、

郵便番号をPrimary Keyにしてはいけません！

郵便番号マスタテーブルをつくっておく

こんなクエリを実行すれば良いかと。

USE [ZIPDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[zipcode_master](
	[code] [nchar](7) COLLATE Japanese_CI_AS NOT NULL,
	[pref] [nvarchar](32) COLLATE Japanese_CI_AS NOT NULL,
	[city] [nvarchar](32) COLLATE Japanese_CI_AS NOT NULL,
	[street] [nvarchar](64) COLLATE Japanese_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_zipcode_master] ON [dbo].[zipcode_master] 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

涙目ながら主キーが設定できないので、ユニークキー制約なしのインデックスを張っておきましょう。

いよいよBULK INSERT

まず、フォーマットテーブルを作っておきます。xmlファイルです。
参考資料はMSDNの該当エントリですが、xmlのインデントが糞なので直しておきました。

<?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="10"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="7"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="7" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="8" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="64"/>
    <FIELD ID="9" xsi:type="CharTerm" TERMINATOR='",' MAX_LENGTH="128"/>
    <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="1"/>
    <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="1"/>
    <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="1"/>
    <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="1"/>
    <FIELD ID="14" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="1"/>
    <FIELD ID="15" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="1"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="3" NAME="code" xsi:type="SQLNCHAR"/>
    <COLUMN SOURCE="7" NAME="pref" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="8" NAME="city" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="9" NAME="street" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

フォーマットファイルを準備したら、下記のクエリでBULK INSERTを実行します。

USE ZIPDB
BULK INSERT dbo.zipcode_master FROM 'S:\KEN_ALL.CSV'
WITH ( FORMATFILE = 'S:\ken_all.xml' )

それにしても

同一郵便番号の重複レコードが最大66件あるってUIの設計涙目じゃん。orz