日本郵便謹製の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