ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Posts Tagged ‘INSERT

郵便番号データを SQL Server で利用するには(2) – データ変換サービス DTS を使わず Access を利用してデータを SQL Server に取り込む

leave a comment »

SQL Server に Excel や Access、CSV形式などのテキストファイルを取り込むために、データ変換サービス、DTS がある。だがデータ変換サービスはデータ型に厳密であるなど、実際の運用においては使いにくい面もある。そこでデータ変換サービス DTS を使わず、Access を利用してデータを SQL Server に取り込むことを紹介する。Access を使うと、取り込みたいデータを確認しながら作業ができるので、データ型やフィールドサイズが不明の場合でも対応しやすい。

ここで SQL Server に取り込みたいデータが、日本郵便からダウンロードできる全国郵便番号であるとする。郵便番号データのダウンロードについては、前回の blog を参考にしてほしい。

 

ダウンロードした csv データファイルを Access に取り込むため、まず Access を起動して空のデータベースを作成する。ここで使用した Access のバージョンは Microsoft Office Access 2007 だが、違うバージョンでもうまくいくはずだ。Access データベースファイルを作成する場所はローカルコンピュータのドキュメントなど適当な場所でいい。

SQL_DTS_Access_001_mid_640

<Fig.1 : Microsoft Office Access 2007 を起動して空のデータベースを作成する。

空のデータベースを作成したら、リボンメニューの「外部データ」から「テキストファイルのインポート」を実行する。日本郵便からダウンロードしたデータが、csv 形式のテキストファイルだからだ。

SQL_DTS_Access_002_mid_640

<Fig.2 : 「外部データ」メニューの「テキストファイルのインポート」を実行する>

データのインポートはウイザードに従ってすすめる。「データのインポート元とインポート先、またはリンク元とリンク先の選択」でデータをインポートする郵便番号ファイルを選択し、データの保存方法は「現在のデータベースの新しいテーブルにソースデータをインポートする」を選ぶ。

SQL_DTS_Access_003_mid_640

<Fig.3 : データのインポート元とインポート先、またはリンク元とリンク先の選択>

ファイルを指定したら読み込むデータが表示されるので、データの区切り形式を選択する。データの区切りは「区切り記号付き」を選ぶ。

SQL_DTS_Access_004_mid_640

<Fig.4 : データインポートウィザードでデータの区切り形式を選択する>

次にフィールド区切り記号を選択する。フィールド区切り記号は「カンマ」を指定する。また「先頭行をフィールド名として使う」オプションは、読み込まれたデータを見てフィールド名に相当するデータがないので、チェックを外す。

SQL_DTS_Access_005_mid_640

<Fig.5 : フィールド区切り記号の選択と先頭行をフィールド名として使うかの選択>

次に読み込むフィールドのフィールド名や型を設定する。Access が読み取ったデータからフィールドの型を類推して決めてくれるが、郵便番号は数値として判断される場合がある。確かに郵便番号は数値なのだが、数値として読み取った場合は先頭のゼロが無視されてしまうので、郵便番号のフィールドは「テキスト型」に変更しておく。

SQL_DTS_Access_006_mid_640

<Fig.6 : フィールドのオプションを設定する>

主キーの設定では、ここでインポートするデータの主キーが不明であるので、「主キーを自動的に設定する」を選んでおく。こうすれば自動的に「ID」のフィールドが作られ、連番が与えられる。

SQL_DTS_Access_007_mid_640

<Fig.7 : 主キーの設定>

インポートウィザードの最終場面では、インポート先のテーブル名を記述する。ここではファイル名から自動的に割り当てられた「KEN_ALL」の名前とした。

SQL_DTS_Access_008_mid_640

<Fig.8 : インポート先のテーブルの指定>

インポートが完了したら、「インポート操作の保存」の画面になる。同じインポート操作を後で繰り返したい場合は、ここで「インポート操作の保存」にチェックをしておくと、簡単に再現できる。ここでは繰り返ししないつもりなので、チェックをしないでおく。

SQL_DTS_Access_009_mid_640

<Fig.9 : インポート操作の保存>

インポートウィザードが終わったら、インポートデータは新たに生成した「KEN_ALL」テーブルに保存されている。テーブルの下部にはレコード数が 123,708 件であることがわかる。

SQL_DTS_Access_010_mid_640

<Fig.10 : インポートウィザードを終了しデータをインポートした>

読み込んだ「KEN_ALL」テーブルのデータを見ると、フィールド1 は意味がよくわからない何らかの管理番号になっているようだ。フィールド2 は郵便番号の前部分、フィールド3 は郵便番号の全6桁、フィールド4 からフィールド6 が住所の読み仮名、フィールド7 からフィールド9 が住所になっている。フィールド10 から15 は何らかの管理フラグだ。

データ構造がわかったら、これに相当するテーブルを SQL Server に作る。CREATE 文は次のようなものとした。

——————————————————————————-

CREATE TABLE 郵便番号住所2(
    管理番号 int NULL,
    プレ郵便番号 varchar(7) NULL,
    郵便番号 char(7) NULL,
    都道府県カナ nvarchar(7) NULL,
    市区町村カナ nvarchar(25) NULL,
    住所それ以降カナ nvarchar(80) NULL,
    都道府県 nvarchar(10) NULL,
    市区町村 nvarchar(25) NULL,
    住所それ以降 nvarchar(80) NULL,
    管理フラグ1 char(1) NULL,
    管理フラグ2 char(1) NULL,
    管理フラグ3 char(1) NULL,
    管理フラグ4 char(1) NULL,
    管理フラグ5 char(1) NULL,
    管理フラグ6 char(1) NULL
)

——————————————————————————-

SQL_DTS_Access_011_mid_640

<Fig.11 : SQL Server Management Studio で郵便番号住所をストアするテーブルを作る>

次に Access を SQL Server のテーブルに「リンクテーブル」として接続する。リボンメニューの「外部データ」から「その他」をクリックし「ODBC データベース」を実行する。

SQL_DTS_Access_012_mid_640

<Fig.12 : リボンメニューの「外部データ」から「その他」をクリックし「ODBC データベース」を実行する

外部データの取り込みウィザードがはじまる。日本郵便のサイトからダウンロードした csv のテキストファイルは Access に取り込んだが、ここでは SQL Server のテーブルにリンクするために「リンクテーブルを作成してソースデータにリンクする」を選択する。

SQL_DTS_Access_013_mid_640

<Fig.13 : 外部データの取り込みウィザードでリンクテーブルを作成してソースデータにリンクする>

「データソースの選択」では、適切な SQL Server へのデータソースがあれば選択するだけでいいが、なければ新規作成する。DNS 名のところの「新規作成」ボタンをクリックする。

SQL_DTS_Access_014

<Fig.14 : データソースの選択>

セットアップするデータソースのドライバーを選択する。接続したい SQL Server を選択して「次へ」をクリックする。

SQL_DTS_Access_015

<Fig.15 : セットアップするデータソースのドライバーを選択する>

データソース名を決めて入力する。データソース名は、後で見てわかるような、適当な名前でいい。

SQL_DTS_Access_016

<Fig.16 : データソース名を決めて入力する>

データソースが作成される。「完了」をクリックする。

SQL_DTS_Access_017

<Fig.17 : データソースの作成>

接続する SQL Server を選択する。「サーバー」テキストボックスの右端▼をクリックすると、利用できる SQL Server が選択できるので、テーブルを作成した SQL Server を選択して「次へ」をクリックする。

SQL_DTS_Access_018

<Fig.18 : 接続する SQL Server を選択する>

SQL Server のログイン認証を選択する。「ネットワークへのログイン ID で、Windows NT の認証メカニズムを使う」はいわゆるWindows 統合認証で、「ユーザーが入力する SQL Server 用のログイン ID とパスワードを使う」は SQL Server にユーザーを作ってログインするものだ。作業している環境によるが、ここでは前者のログインで利用するものとする。

SQL_DTS_Access_019

<Fig.19 : SQL Server のログイン認証を選択する>

既定のデータベースを設定する。郵便番号住所テーブルを作成したデータベース名は「test」だったので、それを選択する。

SQL_DTS_Access_020

<Fig.20 : 既定のデータベースを設定する>

これでデータソースの作成を完了する。

SQL_DTS_Access_021

<Fig.21 : データソースの作成を完了する>

データソースが作成されたら、データソースの詳細が表示される。ここで「データソースのテスト」をクリックすると、SQL Server に接続テストをすることができる。

SQL_DTS_Access_022

<Fig.22 : データソースの作成完了とテスト>

データソースの接続テストが成功した。

SQL_DTS_Access_023

<Fig.23 : SQL Server へのデータソースの接続テスト>

データ接続ができたら、リンクしたいテーブルを指定する。ここでは「郵便番号住所2」テーブルに接続したい。

SQL_DTS_Access_024

<Fig.24 : テーブルのリンク>

リンクテーブルが作成できた。リンクテーブルは地球のようなアイコンで、画面の左側「すべてのテーブル」に「dbo.郵便番号住所2」の名前で表示されている。このテーブルは Access 上に実体はなく、SQL Server のテーブルにリンクしている。

SQL_DTS_Access_025_mid_640

<Fig.25 : リンクテーブルの作成完了>

リンクテーブル「dbo.郵便番号住所2」を開くと、SQL Server 上のデータを見ることができる。まだデータは入っていないので、一件もデータがない。

SQL_DTS_Access_026_mid_640

<Fig.26 : リンクテーブル「dbo.郵便番号住所2」を開いてデータを確かめる>

リンクテーブルが作成できたら、Access にインポートしたデータを、リンクテーブル「dbo.郵便番号2」へコピーする。Access でテーブルへデータをコピーするとき、簡単なのは「追加クエリ」を作成することだ。

「新しいクエリ」を作成し、テーブル「KEN_ALL」を元データとする。画面に表示された「KEN_ALL」テーブルの各フィールドを下段にドラッグし、表示させる。

SQL_DTS_Access_027_mid_640

<Fig.27 : 新しいクエリを作りフィールドを表示させる>

次にこのクエリを「追加クエリ」にする。Access では SQL 文を使ってデータを追加することもできるが、単純にテーブルからテーブルへデータを追加する場合は、追加クエリを使うと便利だ。

クエリを追加クエリにするには、クエリの編集画面で「デザイン」メニューの「+!」のアイコン、「追加」をクリックする。すると「追加」のウインドウが表示され、追加先のテーブルを選択するようになる。追加先はリンクテーブル「dbo.郵便番号2」を選択する。

SQL_DTS_Access_028_mid_640

<Fig.28 : クエリを追加クエリにする>

SQL_DTS_Access_029_mid_640

<Fig.29 : 追加クエリの設定で追加先のテーブルを選択する>

dbo.郵便番号2」をクエリを追加クエリにすると、画面の下部に表示されたフィールドの下の項目が変わる。単なる選択クエリの場合は、「フィールド」、「テーブル」、「並べ替え」、「表示」、「抽出条件」、「または」となっているが、追加クエリにすると「表示」の項目はなくなり、かわりに「レコードの追加」が表示される。これは、データを追加するテーブルのどのフィールドに追加するかを選択する項目だ。各フィールドの「レコードの追加」クリックすると、追加先に設定した「dbo.郵便番号2」の各フィールドが表示されるので、追加したい適切なフィールドを選択し決定していく。追加しなくてもよいフィールドは指定しなくていい。

ここで作成した SQL Server の「dbo.郵便番号2」の場合、「KEN_ALL」テーブルの各フィールドに対して、「ID」を「管理番号」に、「フィールド1」は指定せず、「フィールド2」を「プレ郵便番号」に、以降、フィールド3から「郵便番号」以降を順に設定する。

SQL_DTS_Access_030_mid_640

<Fig.30 : クエリを追加クエリにした>

SQL_DTS_Access_031_mid_640

<Fig.31 : 追加クエリの各フィールドに追加先のフィールドを設定する>

追加クエリができたら保存する。クエリ名は「郵便番号住所の追加」とした。

SQL_DTS_Access_032_mid_640

<Fig.32 : 追加クエリを保存する>

追加クエリが保存できたら、「すべてのテーブル」にあるクエリの名前「郵便番号住所の追加」をダブルクリックする。「追加クエリを実行すると、テーブルのデータが変更されます」となるので「はい」をクリックする。次に追加されるデータの件数が表示されるので、間違いなければ「はい」をクリックする。

SQL_DTS_Access_033_mid_640

<Fig.33 : 保存した追加クエリ「郵便番号住所の追加」をダブルクリックして実行する>

SQL_DTS_Access_034_mid_640

<Fig.34 : 追加クエリを実行してテーブルにデータを追加する>

SQL_DTS_Access_035_mid_640

<Fig.35 : 追加されるデータの件数が表示される>

この追加クエリは、SQL の INSERT 文になっている。SQL 文を確かめるには、追加クエリをデザインし「SQL ビュー」にするといい。次のような SQL 文が生成していることがわかる。

——————————————————————————-

INSERT INTO dbo_郵便番号住所2 ( 管理番号, プレ郵便番号, 郵便番号, 都道府県カナ
              , 市区町村カナ, 住所それ以降カナ, 都道府県, 市区町村, 住所それ以降
              , 管理フラグ1, 管理フラグ2, 管理フラグ3, 管理フラグ4, 管理フラグ5, 管理フラグ6 )
SELECT KEN_ALL.ID, KEN_ALL.フィールド2, KEN_ALL.フィールド3, KEN_ALL.フィールド4
              , KEN_ALL.フィールド5, KEN_ALL.フィールド6, KEN_ALL.フィールド7, KEN_ALL.フィールド8
              , KEN_ALL.フィールド9, KEN_ALL.フィールド10, KEN_ALL.フィールド11, KEN_ALL.フィールド12
              , KEN_ALL.フィールド13, KEN_ALL.フィールド14, KEN_ALL.フィールド15
FROM KEN_ALL;

——————————————————————————-

SQL_DTS_Access_037_mid_640

<Fig.36 : 追加クエリを SQL ビューで確認する>

追加クエリを実行すると、Access にインポートされたデータが SQL Server に追加されているはずだ。「dbo.郵便番号住所2」のリンクテーブルを開いたままなら、一度閉じて再度開くと最新のデータが表示される。

SQL_DTS_Access_038_mid_640

<Fig.37 : 「dbo.郵便番号住所2」のリンクテーブルを開いてインポートされたデータを表示する>

SQL Server Management Studio で直接 SQL Server のテーブルを確かめても、データがインポートされたことが確認できる。

SQL_DTS_Access_039_mid_640

<Fig.38 : SQL Server Management Studio でインポートされたデータを確認する>

このように、Access を使って SQL Server にデータをインポートすることができる。まず Access に CSV データを取り込み、次に「リンクテーブル」で Access を SQL Server に接続し、「追加クエリ」でデータを追加するのだ。

Access の追加クエリは画面上でフィールドを選択することで視覚的にテーブルからテーブルにデータを追加することができるので、SQL Server のデータ処理に使ってもいいだろう。

広告

SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する

leave a comment »

データを管理する際に、そのデータがいつ作られ、いつ更新されたかを記録しておくことが求められる場合がある。これらの日付を記録するため、次のようにテーブルに「作成日」と「更新日」のフィールドを持たせておくことにする。どちらのフィールドも「datetime型」としておこう。

SQL_storedatetime_001_mid_640

<fig.1 : テーブル「Table_3」に「作成日」と「更新日」のフィールドを作成した>

このテーブルに新しいデータを生成し、「作成日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。このとき、getdate()はSQL Serverから日付を取得する関数だ。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘ほげほげ’,getdate())

————————————————————————

SQL_storedatetime_002_mid_640

<fig.2 : 明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_003_mid_640

<fig.3 : Table_3に作成日が登録された>

すでに生成したデータを更新し、「更新日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。

————————————————————————

update Table_3 set サンプル値 = ‘ほにゃらら’,更新日 = getdate() where サンプル値 = ‘ほげほげ’

————————————————————————

SQL_storedatetime_004_mid_640

<fig.4 : Table_3のデータ「ほげほげ」を「ほにゃらら」に更新し、明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_005_mid_640

<fig.5 : Table_3のデータを更新し、更新日が登録された>

新しいデータを作成したときに自動的に「作成日」が記録されるようにするには、フィールドのプロパティで「規定値」を設定する方法がある。規定値に「getdate()」関数を設定しておけばいい。規定値を設定すれば、SQL文で明示的に日付を与えなくてもテーブルには自動的に日付が入るようになる。

SQL_storedatetime_006_mid_640

<fig.6 : Table_3の「作成日」フィールドのプロパティで「規定値」を「getdate()」に設定した>

フィールドに規定値を設定すると、次のSQL文で新しいデータを生成すると「作成日」の値が自動的に記録される。

————————————————————————

insert into Table_3(サンプル値) values(‘規定値でほげ’)

————————————————————————

SQL_storedatetime_007_mid_640

<fig.7 : Table_3に「作成日」を明示的に指定せずにデータを生成した>

SQL_storedatetime_008_mid_640

<fig.8 : Table_3に「作成日」を明示的に指定せずにデータを生成し、規定値が記録された>

このように規定値を使って自動的に値を与えることは一般的だが、この方法ではうまくいかない場合がある。いくら規定値を指定していても、規定値はあくまでも値が示されないときのものであって、ユーザーが明示的に値を与えると規定値は無視されユーザーの指定した値が記録されるのだ。たとえばユーザーが次のSQL文を使うと「記録日」が期待どおりの「規定値」にならない。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘昔の日付で出ています’,’1961/8/13′)

————————————————————————

SQL_storedatetime_009_mid_640

<fig.9 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定してデータを生成するSQL文を実行した>

SQL_storedatetime_010_mid_640

<fig.10 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定して過去の日付でデータを生成した>

データを管理するためには、ユーザーが「作成日」に勝手な値を指定できないようにしたほうがいい。そこでユーザーが「作成日」に明示的な日付を設定しても、それを無効にしてサーバーサイドで日付を強制的に設定される方法を考えてみよう。それにはテーブルにINSERTトリガを設定する方法がある。INSERTトリガは、テーブルにデータがINSERTされたときに自動的に実行されるストアドプロシージャだ。

INSERTトリガが実行されるタイミングは2つある。ひとつはINSERTが実行された後にトリガが実行されるAFTER INSERT、もうひとつはINSERTが実行される前にトリガが実行されるINSTEAD OF INSERTだ。INSTEAD OF INSERTはその名前からわかるように、実際のINSERT文に代わって実行されるトリガだ。ためしに次のSQL文でトリガをテーブルに設定してみよう。トリガを作成するには、create triggerを使う。

————————————————————————

create trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) values(‘トリガでほげ’,GETDATE())

————————————————————————

このトリガを設定するSQL文で、create triggerの次にくる「trig_01」はトリガ名だ。わかりやすい名前を考えて自分で考えてつけることになる。その後onに続いてどのテーブルに対して設定するのかの「テーブル名」を記述している。そしてinstead of insertがトリガの種類だ。次にasに続けて、INSERTに代わって実行させたいSQL文を記述している。トリガを作成したら、左のペイン「オブジェクトエクスプローラー」の「Table_3」を展開すると「トリガ」のところに「trig_01」という名前のトリガができていることがわかる。

SQL_storedatetime_014_mid_640

<fig.11 : create trigger文でTable_3にINSTEAD OF INSERTトリガを作成した>

これでTable_3に何か値をinsertしようとしても、代わりにINSTEAD OF INSERTトリガが働き、あらかじめ決められた内容のデータしか生成しなくなる。たとえば次のようなSQL文をTable_3に実行してみよう。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘どうなる’,’1961/8/13′)

————————————————————————

SQL_storedatetime_015_mid_640

<fig.12 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行する>

ひとつのinsert文しか実行していないのに、処理結果が2つあらわれている。トリガが動いたのだ。

SQL_storedatetime_016_mid_640

<fig.13 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行した結果>

Table_3を見ると、明示的に実行されたINSERT文は無視され、トリガによって強制的にデータがinsertされたことがわかる。「作成日」はgetdate()で得られた日付になっている。

ここで「作成日」が強制的にgetdate()の値になったことはよいが、「サンプル値」が決まった値では困る。「サンプル値」はもとのinsert文で示された値でなければいけない。このときINSERTトリガでは、もとのinsert文によって与えられたデータは、一時的なテーブル「INSERTED」に保存されている。トリガを変更し、INSERTEDテーブルの値を利用して「サンプル値」を取得しよう。トリガを変更するには、ALTER TRIGGERを使う。

————————————————————————

alter trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) select サンプル値,getdate() from inserted

————————————————————————

このINSTEAD OF INSERTトリガで、「サンプル値」はinsert文によって一時テーブルinsertedに記録された「サンプル値」を得、「作成日」はgetdate()で与えている。

SQL_storedatetime_017_mid_640

<fig.14 : alter triggetによってINSTEAD OF INSERTトリガを変更した>

SQL_storedatetime_018_mid_640

<fig.15 : INSTEAD OF INSERTトリガを変更したテーブルにINSERT文を実行した>

SQL_storedatetime_019_mid_640

<fig.16 : 「サンプル値」をinserted一時テーブルから取得し「作成日」はgetdate()で明示的に値を与えた結果>

このように、テーブルにデータを生成するとき「作成日」を与えたいとき、ひとつはフィールドのプロパティで「規定値」を設定する方法があり、さらにユーザーによる「作成日」の明示的な指定を避けたいときは、INSTEAD OF INSERTトリガを設定し、insertされた値を強制的に変更する手法を使うことができる。INSTEAD OF INSERTトリガだけでなく、トリガは他にもいろいろな自動化処理をするために使えるので、データの整合性をとったり履歴を記録したりすることに使うなど、工夫のしがいがあるところだ。

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

with one comment

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。