SQL Server 008 自習書シリーズ No.3 データのコピーと現場で役立つ便利な操作集 Published: 008 年 4 月 30 日 改訂版 : 008 年 月 30 日 有限会社エスキューエル クオリテゖ
この文章に含まれる情報は 公表の日付の時点での Microsoft Corporation の考え方を表しています 市場の変化に応える必要があるた め Microsoft は記載されている内容を約束しているわけではありません この文書の内容は印刷後も正しいとは保障できません この文章 は情報の提供のみを目的としています Microsoft SQL Server Visual Studio Windows Windows XP Windows Server Windows Vista は Microsoft Corporation の米国 およびその他の国における登録商標です その他 記載されている会社名および製品名は 各社の商標または登録商標です Copyright 008 Microsoft Corporation. All rights reserved.
目次 STEP. 自習書を試す環境について... 4. この自習書の内容について... 5. 自習書を試す環境について... 6.3 事前作業 ( サンプルスクリプトのダウンロードとセットゕップ )... 7 STEP. データのコピーとエクスポート... 9. テーブルをコピーする : SELECT.. INTO...0. データをコピーする : INSERT.. SELECT...5.3 クエリエデゖタで CSV 形式の結果を出力する...7.4 bcp コマンドで CSV フゔル出力 ( エクスポート )...0.5 bcp コマンドで CSV フゔル入力 ( ンポート )... STEP 3. Integration Services によるエクスポートとンポート...4 3. Integration Services の概要...5 3. Integration Services による CSV フゔルへのエクスポート...8 3.3 Integration Services による CSV フゔルのンポート...33 3.4 Access データベース (.mdb) のンポート...4 3.5 OPENROWSET による外部データのクエリ...5 3.6 OPENROWSET.. BULK によるテキストフゔルのンポート...56 3.7 リンクサーバーによる外部データのクエリ...59 STEP 4. テーブル関連の便利な操作...64 4. 自動的に連続番号を振る : IDENTITY プロパテゖ...65 4. データの全削除を高速に実行する : TRUNCATE TABLE...7 4.3 列に既定値を設定する : DEFAULT 値...73 3
STEP. 自習書を試す環境について この STEP では この自習書で取り扱う内容と 自習書を試す環境について説明 します この STEP では 次のことを学習します この自習書の内容について 自習書を試す環境について 事前作業 ( サンプルスクリプトのダウンロードとセットゕップ ) 4
. この自習書の内容について この自習書の内容について この自習書では データやテーブルを単純にコピーする方法や CSV カンマ区切り フゔ ルへ の出力 / 入力 外部データ リモートの SQL Server や Access データベース Excel フゔ ル Oracle データベースなど に対するクエリの実行方法 一括 ンポート時の MERGE ステ ートメント連携など 現場で必ず役立つ便利な操作方法について説明しますので ぜひチャレンジ してみください この自習書で取り扱う主な内容は 次のとおりです データとテーブルのコピー SELECT.. INTO INSERT.. INTO CSV フゔ ル形式での出力 bcp コマンドによる CSV フゔ ルの ンポートとエクスポート Integration Services ウゖザードによる ンポート / エクスポート OPENROWSET を利用した外部データのクエリ Access Excel Oracle など 一括 ンポート時の MERGE ステートメント連携 リンク サーバーを利用した外部データのクエリ テーブルの自動連番の設定 IDENTITY プロパテゖ 列の既定値の設定 DEFAULT 値 なお SQL Server 間でデータベースを移動するシナリオ 開発機から本番機 あるいはその逆へ データベースを移動する方法 については 本自習書シリーズの バックゕップと復元 で 詳し く説明しています 5
. 自習書を試す環境について 必要な環境 この自習書で実習を行うために必要な環境は次のとおりです OS Windows Server 003 SP Service Pack 以降 または Windows XP Professional SP 以降 または Windows Vista または Windows Server 008 ソフトウェア SQL Server 008 Enteprise / Developer / Standard / Workgroup Edition この自習書内での画面やテキストは OS に Windows Server 003 SP ソフトウェゕに SQL Server 008 Enterprise Edition を利用して記述しています そのほか この自習書を試すには サンプル スクリプトをダウンロードして 次のページの事前作業を実 行しておく必要があります 6
.3 事前作業 サンプル スクリプトのダウンロードとセットアップ 事前作業 自習書を進めるには サンプル スクリプトをダウンロードしておく必要があります また 自習 書を進めるにあたっての事前作業として Management Studio のクエリ エデゖタからサンプル スクリプト内にある CreateTables.txt を実行して sampledb データベースと 社員 テーブルを作成しておく必要があります 実行手順は 次のとおりです. Management Studio を起動するには スタート メニューの すべてのプログラム か ら Microsoft SQL Server 008 を選択して SQL Server Management Studio をク リックします. 起動後 サーバーへの接続 ダ ゕログで サーバー名 へ SQL Server の名前を入力し 接続 ボタンをクリックします 3. 接続完了後 Management Studio が開いたら 次のようにツールバーの 新しいクエリ をクリックして クエリ エデゖタを開きます 新しいクエリ をクリックします クエリ エディタ が表示される 7
4. 次に Windows エクスプローラを起動して サンプル スクリプトをダウンロードしたフォ ルダを展開し このフォルダ内の CreateTables.txt フゔ ルをダブル クリックして開き ます フゔ ルの内容をすべてコピーして クエリ エデゖタへ貼り付けます サンプル スクリプト内の CreateTables.txt フゔ ルの内容をコピー して貼り付け 3 結果を確認 貼り付け後 ツールバーの 実行 ボタンをクリックしてクエリを実行します これにより sampledb という名前のデータベースが作成され その中へ 社員 テーブルが作成さ れます 実行後 社員 テーブルの 6 件のデータが表示されれば 実行が完了です 8
STEP. データのコピーとエクスポート この STEP では SELECT ステートメントで取得した結果をもとに 新しくテー ブルを作成したり 別のテーブルへコピーしたり CSV フゔ ル形式へエクスポ ートする方法について説明します この STEP では 次のことを学習します テーブルをコピーする SELECT.. INTO データをコピーする INSERT.. SELECT クエリ エデゖタで CSV 形式の結果を出力する bcp コマンドで CSV フゔ ルへの出力 エクスポート bcp コマンドで CSV フゔ ルからの入力 ンポート 9
. テーブルをコピーする SELECT.. INTO SELECT.. INTO SQL Server では SELECT.. INTO SELECT ステートメントで INTO 句 を利用すると SELECT ステートメントで取得した結果をもとにテーブルを作成することができます これは 次 のように記述します SELECT * INTO 新しいテーブル名 FROM テーブル名 Let's Try それでは これを試してみましょう. まずは スタート メニューの すべてのプログラム から Microsoft SQL Server 008 を選択し SQL Server Management Studio をクリックして Management Studio を 起動します. SQL Server への接続画面が表示されたら サーバーの種類 で データベース エンジン を選択し サーバー名 で SQL Server の名前を入力します 認証 では Windows 認証 を選択して 接続 ボタンをクリックします 認証につ いては 本自習書シリーズの ログ ン認証とオブジェクト権限 で詳しく説明しています 0
3. Management Studio が起動したら 次のようにツールバーの 新しいクエリ ボタンをク リックして クエリ エディタ を開きます 新しいクエリ をクリック クエリ エディタ が表示される 4. クエリ エデゖタでは 次のように入力して sampledb データベースへ接続し 社員 テ ーブルのデータを参照します USE sampledb SELECT * FROM 社員!実行 をクリック sampledb データベースへ接続 SQL を記述 社員テーブルを検索 3 社員テーブルの 中身が表示される 社員 テーブルの 6 件のデータを取得できたことを確認できます 5. 次に SELECT.. INTO を利用して 社員 テーブルのデータをもとに 新しく 社員コピ ー テーブルを作成してみましょう USE sampledb SELECT * INTO 社員コピー FROM 社員
6 行処理されました と表示されれば 正しくデータのコピーが完了しています このよう に 通常の SELECT ステートメントへ INTO 新しいテーブル名 を追加するだけで SELECT ステートメントで取得した結果をもとに INTO 句で指定したテーブルを作成でき るようになります 6. コピーした 社員コピー テーブルのデータを確認しておきましょう SELECT * FROM 社員コピー 社員 テーブルのデータが すべてコピーされていることを確認できます 次に 作成した 社員コピー テーブルの定義を確認してみましょう 次のように オブジェ クト エクスプローラで sampledb データベースの テーブル フォルダを右クリックして 最新の情報に更新 をクリックします 7. 列名とデータ型 NULL を許可 するかどうかは 元のテーブル と同じようにコピーされている 社員コピー テーブルが表示されたら これを展開し 列名やデータ型 NULL を許可する
かどうかなどの定義が コピー元のテーブルと同じであることを確認できます Note SELECT.. INTO では制約はコピーされない SELECT.. INTO では 制約 主キー制約や外部キー制約 CHECK 制約など をコピーすることはできません 制約をコピーしたい場合には 別途 次のようにスクリプト生成機能などを利用して コピー先で 制約を再作成 する必要があります 制約のスクリプト生成 制約のスクリプトを生成するには 次のようにテーブルを右クリックして テーブルをスクリプト化 から CREATE 新しいクエリ エディタ ウィンドウ をクリックします これにより テーブルを作成するためのスクリプト CREATE TABLE が生成されて 合わせて 制約を作成す るためのスクリプト ALTER TABLE.. CONSTRAINT も生成されます なお 主キー制約は 次のように CREATE TABLE ステートメントの中へ生成されています 主キー制約もスクリプト化 されている 3
データをコピーせずにテーブル定義のみをコピーする 次に SELECT.. INTO を利用して テーブル定義のみをコピーしてみましょう. 前の手順と同じように SELECT.. INTO を利用して 社員 テーブルのテーブル定義のみ をコピーした 社員コピー という名前の新しいテーブルを作成してみましょう このとき WHERE 句の条件式へ 社員番号 = - を付けて実行するようにします SELECT * INTO 社員コピー FROM 社員 WHERE 社員番号 = - 0 行処理されました と表示されるのがポ ントです 社員 テーブルの中には 社員番 号 が - のデータは存在しないので 0 件と表示されています これにより データは 件もコピーされずに テーブルの定義だけをコピーして 社員コピー テーブルを作成す ることができています このように WHERE 句の条件式で 結果が ０件になるように記述 すると テーブル定義だけをコピーできるようになります なお WHERE 句の条件式へは WHERE = のように 成り立たない条件式を記述した 場合にも 結果が 0 件になるので テーブル定義のみをコピーする目的として利用すること ができます 4
. データをコピーする INSERT.. SELECT INSERT.. SELECT 前述の SELECT.. INTO は データのコピーとともに 新しくテーブルを作成するので 既に存 在するテーブルに対してデータをコピーする目的としては利用できません 既存のテーブルへデー タをコピーしたい場合には INSERT.. SELECT を利用します これは 次のように利用します INSERT INTO テーブル名 SELECT ステートメント これにより SELECT ステートメントで取得した結果を INSERT INTO で指定したテーブルへコ ピーできるようになります Let's Try それでは これを試してみましょう. まずは sampledb データベース内へ 次のように emp という名前のテーブルを作成 します USE sampledb CREATE TABLE emp ( empno int,empname char(50) ). 次に INSERT.. SELECT を利用して 社員 テーブルの 社員番号 と 氏名 列を emp テーブルの empno と empname 列へ それぞれコピーしてみましょう INSERT INTO emp SELECT 社員番号, 氏名 FROM 社員 5
既存の emp テーブルへ 社員 テーブルの 社員番号 と 氏名 列のデータをコピー できたことを確認できます このように INSERT.. SELECT を利用すると 既存のテーブ ルへデータをコピーできるようになります Note INSERT 先のテーブルの列数と SELECT ステートメントで取得する列数を同じにする INSERT.. SELECT では INSERT 先のテーブルの列数と SELECT ステートメントで取得する列数が異なる場合 には エラーが発生します たとえば 次のように列数が異なる場合は エラーが発生して データのコピーに失 敗します INSERT 先のテーブルの列数と SELECT ステートメントで取得する列数は 合わせるようにしましょう 6
.3 クエリ エディタで CSV 形式の結果を出力する クエリ エディタで CSV 形式の結果を出力 Management Studio の クエリ エデゖタ では SELECT ステートメントで取得した結果を CSV 形式 カンマ区切りのテキスト形式 へ変換して出力することができます Let's Try それでは これを試してみましょう. まずは 次のようにクエリ エデゖタ上の任意の領域を右クリックして 結果の出力 の 結 果をテキストで表示 をクリックします 結果をテキストで表示 を クリック デフォルトは 結果をグリッドに表示 これで グリッド形式ではなく テキスト形式でクエリ結果を取得できるようになります. 続いて もう一度 クエリ エデゖタ上の任意の領域を右クリックして クエリ オプション をクリックします クエリ オプション をクリック 7
3. これにより クエリ オプション ダ ゕログが表示されるので 左ペ ンで 結果 を展開 して テキスト をクリックします テキスト を選択 出力形式 で コンマ 区切り を選択 デフォ ルトは 固定列 3 次に 右ペ ンの 出力形式 で コンマ区切り を選択して OK ボタンをクリックしま す 以上で 設定が完了です 4. それでは 社員 テーブルのデータを参照してみましょう USE sampledb SELECT * FROM 社員 結果が カンマ区切りの CSV 形式で出力されたことを確認できます このように クエリ エ デゖタの出力形式を コンマ区切り へ設定すると SELECT ステートメントの実行結果を CSV 形式で取得できるようになります Note char 型の余分なスペースをトルには RTRIM 関数 上の手順では 氏名 列のデータの右側へ余分なスペースが含まれています これは 氏名 列のデータ型を char(50) で定義しているためです この右側の余分なスペースを取り除くには RTRIM という関数を次のよ うに利用します 8
RTRIM 関数については 本自習書シリーズの Transact-SQL 入門 で詳しく説明しています 5. 最後に デフォルトのグリッド形式で結果が出力されるように戻しておきましょう グリッド 形式へ戻すには 次のようにクエリ エデゖタ上の任意の領域を右クリックして 結果の出力 の 結果をグリッドに表示 をクリックします 9
.4 bcp コマンドで CSV ファイル出力 エクスポート bcp コマンドで CSV ファイル出力 エクスポート SQL Server のデータを CSV 形式のフゔ ルへ出力するには bcp コマンドを利用することも できます bcp コマンドの構文は 次のとおりです bcp テーブル名 out CSVファイル名 /S SQL Server 名 /T /c /t "," 指定しているオプションの役割は 次のとおりです オプション 役割 /S SQL Server の名前 /T Windows 認証でSQL Server へ接続 /c テキスト形式で出力 /t 列区切りを示す記号を指定, と記述した場合はカンマ区切り 省略時は Tab 区切り /S で SQL Server の名前を指定し /T で Windows 認証での接続 /c でテキスト形式での出 力 /t, でカンマ区切りを指定できます /T と /t は 異なるオプションなので 大文字と小 文字が区別されるので 注意してください bcp コマンドは コマンド プロンプト ツールから実行する必要があります コマンド プロン プトは 次のように スタート メニューの アクセサリ から コマンド プロンプト をクリ ックして 起動することができます Let's Try それでは これを試してみましょう 0
. まずは スタート メニューからコマンド プロンプトを起動します. コマンド プロンプトが起動したら 次のように bcp コマンドを入力して 社員 テーブル のデータを C:\shain.txt フゔ ル CSV 形式 へ出力してみましょう bcp sampledb.dbo.社員 out C:\shain.txt /S Server /T /c /t, bcp コマンドを入力後 ENTER キーを押す /S オプションに指定した Server は SQL Server の名前 ンスタンス名 になるの で 皆さんの環境に合わせて変更してください また テーブル名の指定は データベース名. スキーマ名.テーブル名 と記述する必要があることに注意してください スキーマにについ ては 本自習書シリーズの ログ ン認証とオブジェクト権限 で詳しく説明しています コマンドを実行後 コピーを開始しています... と表示されて その後 n 行コピーされま した と表示されれば成功です. 次に Windows エクスプローラを起動して C:\shain.txt フゔ ルが作成されているこ とを確認します ダブル クリック メモ帳で C: shain.txt フゔ ル を開いて 結果を確認 氏名 列 は char 型なので余分な空白あり CSV 形式でフゔ ルが作成されていることを確認できます Note bcp は何の略 bcp は Bulk Copy の略です Bulk は 一括 / 大量 / バラ積み という意味なので bcp は 一括コピー プ ログラム と呼ばれることもあります
.5 bcp コマンドで CSV ファイル入力 インポート bcp コマンドで CSV ファイル入力 インポート bcp コマンドでは テキスト フゔ ルを ンポートする機能もあります これは 次のように利 用します bcp テーブル名 in CSVファイル名 /S SQL Server 名 /T /c /t, 出力するときとの違いは out を in へ変更しただけです Let's Try それでは これを試してみましょう. 前の step で出力した C:\shain.txt を Step.１で作成した 社員コピー２ テーブル へ ンポートしてみましょう コマンド プロンプトを起動して 次のように入力します bcp sampledb.dbo.社員コピー in C:\shain.txt /S Server /T /c /t "," コマンドの実行後 n 行コピーされました と表示されれば成功です. 次に クエリ エデゖタを開いて 社員コピー テーブルを参照して データが正しく ン ポートされているかどうかを確認しておきましょう USE sampledb SELECT * FROM 社員コピー
Note bcp コマンドの注意点 bcp コマンドは 単純なデータのコピー用途としては便利ですが 複雑な処理を行うのには適していません たと えば ンポート元の CSV フゔ ルの列数とテーブルの列数が異なる場合や データにカンマが含まれている場 合には フォーマット ファイル を利用しなければなりません データにカンマが含まれている場合は そのカン マが区切り文字として見なされないようにデータを二重引用符で囲むなどの対処が必要になります これらの問 題は 次の Step で説明する Integration Services 機能を利用すると簡単に解決することができます 3
STEP 3. Integration Services による エクスポートと ンポート この STEP では Integration Services の ンポート / エクスポート ウゖザー ドを利用したデータのコピーや OPENROWSET リンク サーバーを利用した外 部データのクエリ方法について説明します この STEP では 次のことを学習します Integration Services の概要 CSV フゔ ルへのエクスポート CSV フゔ ルの ンポート Access データベース.mdb の ンポート OPENROWSET を利用した外部データのクエリ リンク サーバーを利用した外部データのクエリ 4
3. Integration Services の概要 Integration Services の概要 SQL Server 008 Integration Services SSIS データ統合サービス は SQL Server の標準 機能として搭載されている データの コピー や 変換 などが行える データ転送ツール で す SQL Server 000 以前のバージョンでは DTS Data Transformation Services データ変 換サービス と呼ばれていました Integration Services を利用すると SQL Server 同士でのデータ転送はもちろん Oracle や DB Microsoft Office Access そのほかの ODBC 対応のデータベース Microsoft Office Excel フゔ ル 可変長のテキスト ファイル カンマ区切り タブ区切り 固定長のテキスト フゔ ルなど さまざまなデータソースから SQL Server へデータを取り込んだり それとは逆に SQL Server からデータを書き出したりすることができます Integration Services は データ転送 変換ツール Excel フゔ ル Accessフゔ ル テキスト フゔ ル さまざまなデータソースとの間で データのコピーと変換が可能 メ ン フレーム 汎用機 Oracle DB MySQL など 前の Step で紹介した bcp コマンドは テキスト フゔ ルとの間での単純なデータ コピーだけ しか行えませんが Integration Services を利用すると 次のように Access や Excel フゔ ル Oracle などの間で データのコピーが行えます Access データベース.mdb フゔ ル を SQL Server へ取り込むことが可能 Access データベース.mdb SQL Server 008 コピー 5
Excel データ.xls フゔ ル を SQL Server へ取り込む ンポートする Excel フゔ ル SQL Server 008 データ コピー Oracle データベースを SQL Server へ取り込む SQL Server 008 Oracle データベース コピー インポート / エクスポート ウィザード Integration Services では これらのデータ転送は インポート / エクスポート ウィザード という機能によって ウゖザード形式 対話形式 で指定された項目を入力 選択するだけで簡単 に実行することができます ンポート / エクスポート ウゖザード 6
SSIS デザイナによるデータ変換 Integration Services には 単純なデータ コピーだけでなく 次のように データを変換 デー タを加工しながら転送 できる機能もあります データを変換して 転送する 姓 と 名 を 文字列連結して 氏名 へ変換 性別コード の を 男性 へ を 女性 へ変換 部門番号 を 部門名 へ変換 この機能は 非常に便利で データ ウェゕハウス DWH Data Warehouse を構築する際には 欠かせないツールとなります このようなデータ変換は SSIS デザイナ ツールを利用して行えます SSIS デザ ナを利用 すると データの転送に加えて 複雑な変換処理を追加したり 次のように転送の前後に行いたい 処理 FTP によるフゔ ル取得やメール送信 SQL の実行など を追加したりできるようになり ます SSIS デザ ナ FTP でフゔ ルを取得し たり 複数のフゔ ルをま とめて処理する Foreach Loop メールを送信した りすることもできる さまざまなタスク を実行可能 SSIS デザ ナについては この自習書では説明していませんが 本自習書シリーズの Integration Services 入門 で詳しく説明しているので ぜひご覧いただければと思います 自習書シリーズ Integration Services 入門 のダウンロードはこちらから http://www.microsoft.com/japan/sqlserver/008/self-learning/default.mspx#cat0 以降では Integration Services の インポート / エクスポート ウィザード を利用して CSV フゔ ルとやり取りする方法と Access データベース.mdb を ンポートする方法を説明しま す 7
3. Integration Services による CSV ファイルへのエクスポート インポート / エクスポート ウィザードによるエクスポート それでは Integration Services を試してみましょう まずは インポート / エクスポート ウ ィザード を利用して sampledb データベースの 社員 テーブルのデータを CSV カン マ区切り 形式のフゔ ルへエクスポートしてみましょう. ンポート / エクスポート ウゖザードを起動するには 次のように Management Studio でコピー元のデータベース sampledb を右クリックして タスク メニューの データ のエクスポート をクリックします. データのエクスポート をクリック これにより ンポート / エクスポート ウゖザードが起動されるので 次へ ボタンをク リックして 次へ進みます 8
3. 次の データソースの選択 画面では コピー元となるデータを指定します sampledb を選択 今回は 事前に sampledb データベースを選択してウゖザードを起動しているので デ ータベース に sampledb が選択されていることを確認できます また データソース には SQL Server Native Client 0.0 が選択されていますが これは SQL Server 008 をコピー元にするという意味です サーバー名 には SQL Server の名前が選択されている ことを確認して 次へ ボタンをクリックします 4. 次の 変換先の選択 画面では 変換先 エクスポート先 を指定します フラット フゔ ル 変換先 を選択 出力先となるフゔ ルの 名前を入力 3 テキスト修飾子に 二重引用符 を入力 4 今回は CSV フゔ ルへデータをエクスポートするので 変換先 で フラット ファイル 変換先 を選択します フゔ ル名 へは エクスポート先となる任意のフゔ ル名 今回 は C:\shain.txt を入力します また テキスト修飾子 に " 二重引用符 を入力 9
して データを " で囲むようにします 設定後 次へ ボタンをクリックします 5. 次の テーブルのコピーまたはクエリの指定 画面では つ以上のテーブルまたはビュー からデータをコピーする がチェックされていることを確認して 次へ ボタンをクリック します つ以上のテーブルまた はビューからデータをコ ピーする を選択 もし こちらを選択した場合は SELECT ステートメントを記述して その結果を 変換先へコピーできる 6. 次の フラット フゔ ル変換先の構成 画面では コピー元となるテーブルを選択します 今回は 変換元テーブルまたはビュー で 社員 テーブルを選択します 3 また 列区切り記号 で コンマ{,} が選択されていることを確認します これで 社員 テーブルを CSV 形式へエクスポートできるようになります 設定後 次へ ボタンをクリ ックします 7. 次の パッケージの保存および実行 画面では このデータ転送をすぐに実行するか 後から 30
実行するために保存するかを選択します すぐに実行する を選択 今回はすぐに実行するので すぐに実行する がチェックされていることを確認して 次へ ボタンをクリックします 8. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックすると データのエクスポート が開始されます 9. 完了 をクリック エクスポートが完了すると 次のように 操作は正常に実行されました 画面が表示されます 状態 が 成功 と表示されていることを確認して 閉じる ボタンをクリックします 3
エクスポートしたデータの確認 0. ウゖザードでエクスポートしたデータを確認するには Windows エクスプローラを起動して C:\shain.txt フゔ ルをメモ帳で開きます コピーの完了後 C: shain.txt フゔ ルをダブル クリックしてメモ帳で開く 各列のデータが 重引用符 で囲まれていることを確認 それぞれの列がカンマ区切りで表示され データが " 重引用符 で囲まれていることを 確認できます データを で囲んでおくと データ内にカンマがあったとしても それを区 切り記号と間違えないようになります このように Integration Services を利用すると CSV フゔ ルへのエクスポートを GUI 操 作だけで簡単に行うことができるようになるので 大変便利です 3
3.3 Integration Services による CSV ファイルのインポート インポート / エクスポート ウィザードでのインポート ンポート / エクスポート ウゖザードでは CSV フゔ ルを SQL Server 内へ ンポートする ことも簡単に行えます Let's Try それでは これを試してみましょう 前の Step でエクスポートした C:\shain.txt フゔ ルを SQL Server へ ンポートしてみましょう. まずは スタート メニューの すべてのプログラム Microsoft SQL Server 008 から データのインポートおよびエクスポート 3 ビット をクリックします. ンポート エクスポート ウゖザードが起動されたら 次へ ボタンをクリックします 33
. 次の データソースの選択 画面では データのコピー元を選択します コピー元がテキスト フゔ ルの場合は データソース で フラット ファイル ソース を選択します 続いて フゔ ル名 の 参照 ボタンをクリックして コピー元となるフゔ ルを選択し ます 次のように フゔ ルの場所 で ローカル ディスク C: を選択します 3 C: ドラ ブ内のすべてのフゔ ルが表示されたら C:\shain.txt フゔ ルを選択して 開く ボタンをクリックします 3. データソースの選択 画面へ戻ったら 次のように フゔ ル名 へ Shain.txt へのパ スが表示されているのを確認します 34
テキスト修飾子に 二重引用符 を入力 テキスト修飾子 へは " 重引用符 を入力します 4. 続いて 次のように 列 ページをクリックして開きます 列 ページでは テキスト フゔ ルのデータ形式を設定することができます 3 列名が 列x と 命名されている 4 5 行区切り記号 に "改行" を表す {CR}{LF} が選択され 列区切り記号 にコンマ {,} が選択されていることを確認します 今回取り込むフゔ ルは CSV カンマ区切り フゔ ルなので このままの設定で大丈夫です 6 行のプレビュー には コピー元の CSV フゔ ルのデータが表示され 列名が自動 的に 列 0 列 列 列 3 列 4 と命名されていることを確認できます 確認後 次へ 35
ボタンをクリックします 5. 次の 変換先の選択 画面では コピー先を指定します 今回は SQL Server へ ンポート するので 変換先 で SQL Server Native Client 0.0 を選択し サーバー名 へ SQL Server の名前を入力します データベース へは ンポート先となるデータベースを選択しますが ここでは 新規作 成 ボタンをクリックして 新しくデータベースを作成します データベースの作成 ダ ゕログが表示されたら 名前 へ新しく作成する任意のデータ ベース名 今回は SSISDB を入力し OK ボタンをクリックします これで SSISDB という名前のデータベースが作成されます 36
6. 変換先の選択 画面へ戻ったら 次へ ボタンをクリックして次の画面へ進みます 7. 次の コピー元のテーブルおよびビューを選択 画面では ンポート元と ンポート先とな るテーブルを指定します 今回のように ンポート元がテキスト フゔ ルの場合には 変換 元 へフゔ ル パス C:\shain.txt が表示されます 変換先 へは [dbo].[shain] と表示されますが これは SSISDB データベース内へ shain という名前のテーブルを作成するという意味です テーブルは実際にデータが転 送されるときに自動作成されます テーブル名は ここで変更することもできますが 今回 は shain という名前のままにしておきます 8. 続いて マッピングの編集 ボタンをクリックします すると 次のように 列マッピング 37
ダ ゕログが表示されて 作成されるテーブルの列名やデータ型などを変更できるようになり ます マッピングの編集 をクリックした場 合はコピーしない列を設定したりできる CSV フゔ ルの列数とテーブルの列数が 異なる場合に役立つ 既定では テキスト フゔ ルのデータは varchar データ型として設定され 列名は 左側 から順に 列 0 列 と設定されます 今回は 変換先 ンポート先 の列名をわかりや すくするために 次のように 列 0 を 社員番号 へ 列 を 氏名 へ 列 を 給 与 へ変更します 列 3 と 列 4 は <無視> を選択して コピー対象から外すようにします このダ ゕログでは データ型やサ ズなども変更できますが 今回は このままで OK ボタンを クリックします 9. コピー元のテーブルおよびビューを選択 画面へ戻ったら 次へ ボタンをクリックしま 38
す 0. 次の パッケージの保存および実行 画面では すぐに実行する をチェックして 次へ ボタンをクリックします すぐに実行する を選択. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックすると データのエクスポート が開始されます 39
完了 をクリック. ンポートが完了すると 操作は正常に実行されました 画面が表示されます 状態 が 成 功 と表示されていることを確認して 閉じる ボタンをクリックします インポートされたデータの確認 3. ウゖザードで ンポートしたデータを確認するには Management Studio のオブジェクト エクスプローラで データベース フォルダを右クリックして 最新の情報に更新 をクリ ックします SSISDB データベースが表示されたら 次のように テーブル フォルダを 展開して shain という名前のテーブルを表示します 40
4. 続いて shain テーブルを右クリックして 上位 000 行の選択 をクリックします shain のデータが 表示される これにより shain テーブルのデータ 最初の 000 件 が表示されます 社員番号と氏 名 給与がコピーされて 入社日と部門番号がコピーされていないことを確認できます このように Integration Services では ンポート元の CSV フゔ ルの列数とテーブル の列数が異なる場合にも 簡単にコピーすることができます 前の Step で利用した bcp コ マンドで同じことを実現しようとすると フォーマット フゔ ルを作成しなければなりませ ん 4
3.4 Access データベース.mdb のインポート Access データベースのインポート Integration Services を利用すると Access データベース.mdb ファイル も簡単に ンポ ートできるようになります Let's Try それでは これを試してみましょう ここでは Microsoft Access 003 に付属の Northwind ノース ウ ンド サンプル データベースを SQL Server へ ンポートしてみましょう Northwind データベースは 販売管理を題材としたサンプル データベースで 次のように商品に 関する受注情報が格納されています なお このデータベースは Access の既定の ンストールでは ンストールされていません ンストールするには 次のようにプログラムの追加と削除から行います 4
3 以降の手順を試すには この Northwind データベース Northwind.mdb が必要になります が ほかの.mdb フゔ ルや Access 97 / 000 に付属のデータベースでもほとんど同じように 試せるので Northwind がない場合は 任意の.mdb フゔ ルで試してみてください インポート / エクスポート ウィザードの起動. まずは 次のように Management Studio で SSISDB データベースを右クリックして タスク メニューの データのインポート をクリックします 43
. ンポート / エクスポート ウゖザードが起動したら 次へ ボタンをクリックします 3. 次の データ ソースの選択 画面では データソース コピー元となるデータベース に Microsoft Access を選択します 続いて フゔ ル名 の 参照 ボタンをクリックして フゔ ルを開く ダ ゕログを表 示します 44
3 Northwind サンプル データベースが置いてある C:\Program Files\Microsoft Office \OFFICE\SAMPLES フォルダを展開し Northwind.mdb フゔ ルを選択して 開 く ボタンをクリックします 4. データソースの選択 画面へ戻ったら 次へ ボタンをクリックします Note Access 007 データベース.accdb を取り込みたい場合 Access 007 のデータベース.accdb フゔ ル からデータを取り込みたい場合は [データ ソース] で Microsoft Office.0 Access Database Engine OLE DB Provider を選択する必要があります このプ ロバ ダは Microsoft Office 007 の ンストール時に自動で ンストールされます ンポート / エクスポー ト ウゖザードを実行しているマシンに Microsoft Office 007 を ンストールしていない場合は 下記のサ トか ら 007 Office system ドラ バ をダウンロードして ンストールすることで 利用できるようになります 007 Office system ドラ バ: データ接続コンポーネント http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c8-4598-9b7-ef94e038c89 &displaylang=ja 45
Microsoft Office.0 Access Database Engine OLE DB Provider を利用して Access 007 の.accdb へ接続している例 3 今回利用している.mdb フゔルのように Access 003 以前のバージョンのデータベースの場合には [ データ ソース ] へ Microsoft Access を選択すれば大丈夫です 5. 次の [ 変換先の選択 ] 画面では 変換先 ( コピー先 ) の設定を行います 今回は 事前に SSISDB データベースを選択してウゖザードを起動しているので [ デー タベース ] へ SSISDB が選択されています ここでは このまま [ 次へ ] ボタンをクリックして SSISDB データベースへデータをン ポートします 46
6. 次の テーブルのコピーまたはクエリの指定 画面では つ以上のテーブルまたはビュー からデータをコピーする を選択して 次へ ボタンをクリックします 7. 次の コピー元のテーブルおよびビューを選択 画面では Access データベース内のテー ブルが一覧されます 今回は 仕入先 と 社員 受注 受注明細 商品 商品区 分 得意先 の 7 つのテーブルをチェックして ンポート対象に設定します 設定後 次へ ボタンをクリックします 47
8. 次の パッケージの保存および実行 画面では すぐに実行する をチェックして 次へ ボタンをクリックします このデータのコピーをすぐに実行するか 後から実行するために保 存するかを選択します 9. 最後の ウゖザードの完了 画面で 完了 ボタンをクリックすると データのエクスポート が開始されます 0. ンポートが完了すると 次のように 操作は正常に実行されました 画面が表示されます 状態 が 成功 と表示されていることを確認して 閉じる ボタンをクリックします 48
インポートされたデータの確認. ンポートしたデータを確認するには Management Studio のオブジェクト エクスプロー ラで SSISDB データベースを展開して テーブル フォルダを右クリックして 最新の 情報に更新 をクリックします これにより ンポートした 7 つのテーブル 仕入先 社 員 受注 受注明細 商品 商品区分 得意先 が表示されるようになります. 次に 社員 テーブルや 商品 テーブルを右クリックして 上位 000 行の選択 をク リックし テーブルの中身を確認しておきましょう 49
社員 テーブルのデータの確認 商品 テーブルのデータの確認 50
Note Access 側で設定された主キーやリレーション シップは転送されない Integration Services では Access データベース側で設定された主キーやリレーション シップなどの制約は転 送されません Access 側で設定されていたリレーション シップは 次のとおりです Integration Services で取り込んだデータを Management Studio で確認すると 次のようになります 社員テーブルの列の一覧 主キーと外部キーには鍵のゕ コンが付くが 設定されて いない 主キーと外部キーが一覧され る場所 ここに存在しないこ とからも主キーと外部キーが 設定されていないことを確認 できる 制約が一覧される 場所 ココも空 [キー] フォルダや [制約] フォルダを参照しても 制約が作成されていないことから 制約が転送されていないこ とを確認できます したがって 主キーやリレーション シップなどは データの転送後に手動で設定する必要が あります 5
3.5 OPENROWSET による外部データのクエリ OPENROWSET 関数とは SQL Server では OPENROWSET という関数を利用すると Access データベースやリモート の SQL Server など さまざまなデータベースのデータを SELECT ステートメントでクエリで きるようになります この機能は アドホック クエリ Adhoc Query とも呼ばれています OPENROWSET 関数は 次のように利用します SELECT * FROM OPENROWSET('プロバイダ名', 'プロバイダに応じた接続パス', 'SELECT ステートメント') アドホック クエリの有効化 OPENROWSET 関数を利用するには 次のようにゕドホック クエリを有効化しておく必要があり ます ゕドホック クエリは 悪意のある攻撃者からの思わぬ侵入経路となりかねないので デフ ォルトでは禁止に設定されているからです EXEC sp_configure 'show advanced options', RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', RECONFIGURE また OPENROWSET による操作が完了した後は 次のようにゕドホック クエリを無効化してお くようにしましょう EXEC sp_configure 'Ad Hoc Distributed Queries', 0 EXEC sp_configure 'show advanced options', 0 RECONFIGURE リモートの SQL Server 008 へ接続する場合 OPENROWSET 関数を利用して リモートの SQL Server 008 のデータをクエリする場合には 次のように記述します SELECT * FROM OPENROWSET('SQLNCLI0', 'Server=POWER;Trusted_Connection=yes;', 'SELECT * FROM sampledb.dbo.社員' ) 5
第 引数の SQLNCLI0 は SQL Server Native Client 0.0 を利用するという意味で 第 引数では Server= に続けて 接続したい SQL Server の名前を指定しています また ; セミコロン を記述して ;Trusted_Connection=yes; と記述することで Windows 認 証での接続を試みることができます セミコロンの部分を カンマにしないように注意してくださ い 第 3 引数には リモートの SQL Server 008 で実行したい SELELCT ステートメントを記 述します このように OPENROWSET 関数を利用すると リモート 外部データ を簡単にクエリするこ とができます また OPENROWSET で取得した結果に対して Step で説明した SELECT.. INTO を利用すれば 結果をもとにテーブルを作成することもできます Access データベース.mdb をクエリする場合 OPENROWSET では Access のデータベース.mdb をクエリすることもできます これは 次のように利用します Access 000/003 データベースへ接続する場合 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE\SAMPLES\Northwind.mdb';'admin';'', 'SELECT * FROM 社員') Access 000/003 へ接続する場合には 第 引数へ Microsoft.Jet.OLEDB 4.0 を指定し 第 引数には.mdb ファイルへのパスを記述します ここでは Northwind サンプル データベ 53
ースへのパスを記述しています パスに続けて ; セミコロン を記述し ;'admin';'' と 指定します これは Access に接続するためのユーザー名とパスワードの指定ですが デフォル トでは admin パスワードなしで接続できます また セミコロンの部分は カンマにしないよ うに注意してください 第 3 引数には "SELECT * FROM 社員" のように Access データベー スに対して実行したい任意の SELECT ステートメントを記述できます Excel データをクエリ OPENROWSET 関数では Excel データ.xls ファイル をクエリすることもできます これは 次のように利用します Excel 003 データの場合 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Shohin.xls;', 'SELECT * FROM [Sheet$]') C: Shohin.xls フゔ ル Sheet のデータ Excel 003 へ接続する場合は 第 引数へ Access のときと同様 Microsoft.Jet.OLEDB 4.0 を指定し 第 引数には Excel 8.0;Database=C:\Shohin.xls; のように記述します Excel 8.0 と指定することで Excel データであることを指定し Database= に続けて.xls フゔ ルへのパスを記述します 第 3 引数には "SELECT * FROM [Sheet$]" のように記述し シ ート名に $ マークを付けて 大カッコ [ ] で囲みます このように OPENROWSET 関数を利用すると Excel データも簡単にクエリすることができま す なお クエリする Excel フゔ ルが Excel によって開かれている場合は エラーになるの で 必ず Excel を終了してから このクエリを実行するようにします Oracle データベースをクエリ OPENROWSET 関数では Oracle データベースをクエリすることもできます これは 次のよう に利用します Oracle 9i データベースの場合 SELECT * FROM OPENROWSET('MSDAORA', 'ORCL';'SCOTT';'tiger', 'SELECT * FROM EMP') 54
Oracle 9i データベースへ接続する場合は 第 引数へ MSDAORA を指定し 第 引数には 'ORCL';'SCOTT';'tiger' のように ネットサービス名 ユーザー名 パスワードの順に記述します 第 3 引数には "SELECT * FROM EMP" のように Oracle Server に対して実行したい任意の SELECT ステートメントを記述できます なお Oracle データベースへ接続するには 事前に Oracle Provider for OLE DB をンストールして ネットサービス名を設定しておく必要があります このように OPENROWSET 関数を利用すると Oracle データベースも簡単にクエリできるようになります 55
3.6 OPENROWSET.. BULK によるテキスト ファイルのインポート OPENROWSET.. BULK でテキスト ファイルを一括インポート OPENROWSET 関数では テキスト フゔ ルを一括 ンポートする機能もあります これは 次 のように利用します SELECT * FROM OPENROWSET( BULK 'テキスト ファイル名',FORMATFILE = 'フォーマット ファイル名' ) 別名 OPENROWSET での一括 ンポートでは フォーマット ファイルを別途作成する必要があります このフゔ ルは bcp コマンドや BULK INSERT というステートメントで利用できるものと同 じ形式なのですが ンポート先の列名や列数と テキスト フゔ ル内の列数や区切り文字など を対応付けするためフゔ ルです これは 次のような形式をとります フォーマット ファイルの自動生成 bcp コマンド bcp コマンドには 既存のテーブルをもとに フォーマット フゔ ルを自動生成してくれる機能 があります たとえば 次のように ンポート先のテーブルがあるとします -- インポート先のテーブル USE sampledb CREATE TABLE t ( a int,b char(0 ) ) この場合に bcp コマンドをコマンド プロンプトから次のように実行すると フォーマット フゔ ルを自動生成してくれます bcp sampledb.dbo.t format nul -T -c -t "," -f C:\test.fmt 自動生成されたフォーマット フゔ ル カンマ区切りの CSV フゔ ル用 Step で利用した bcp コマンドとの違いは format nul と -f オプションだけです 残りのオ 56
プションは 同じ意味で -T で Windows 認証 -c -t "," でカンマ区切りのテキスト フゔ ル 形式であることを指定しています -f オプションでは 生成したいフォーマット フゔ ルの名前 を任意で指定できます 拡張子を.fmt とするのが慣習です OPENROWSET.. BULK での一括インポート 次に OPENROWSET 関数でテキスト フゔ ルを一括 ンポートしてみましょう 次のテキスト フゔ ル カンマ区切り を t テーブルへ一括 ンポートするとします このフゔ ルが C:\bulkTest.csv の場合は OPENROWSET 関数を次のように記述します SELECT * FROM OPENROWSET( BULK 'C:\bulkTest.csv',FORMATFILE = 'C:\test.fmt' ) b これを 次のように INSERT.. SELECT で利用すれば t テーブルへ一括 ンポートできるよ うになります INSERT INTO t SELECT * FROM OPENROWSET( BULK 'C:\bulkTest.csv',FORMATFILE = 'C:\test.fmt' ) b 57
MERGE ステートメントと OPENROWSET.. BULK の連携 OPENROWSET.. BULK は SQL Server 008 からの新機能である MERGE ステートメント と連携して機能させることができます MERGE ステートメントは データが存在する場合には UPDATE を 存在しない場合には INSERT 処理が行える非常に便利ステートメントです 詳し くは 本自習書シリーズの Transact-SQL 応用 で説明しています したがって MERGE ステートメントと OPENROWSET.. BULK を組み合わせると 一括 ンポ ート時に データが既に存在する場合には UPDATE を 存在しない場合には INSERT 処理が行 えるようになります たとえば ンポートしたいフゔ ルが 次の C:\bulkTest.csv だとします この場合は 次のように実行することができます MERGE INTO t USING OPENROWSET( BULK 'C:\bulkTest.csv',FORMATFILE = 'C:\test.fmt' ) bulk ON t.a = bulk.a WHEN MATCHED THEN UPDATE SET t.b = bulk.b WHEN NOT MATCHED THEN INSERT VALUES ( bulk.a, bulk.b ); t テーブル C: bulktest.csv フゔ ル MERGE UPDATE されたデータ INSERT されたデータ このように OPENROWSET 関数は テキスト フゔ ルを一括 ンポートするシナリオでも大変 役立ちます 58
3.7 リンク サーバーによる外部データのクエリ リンク サーバー OPENROWSET 関数では 毎回クエリの実行時にプロバ ダ名や接続パスを記述する必要がある ので 何度もクエリを実行する場合には面倒です これを解決してくれる機能が リンク サーバ ー です リンク サーバーを利用すると 何度もゕクセスするリモート データ 接続先 に対し て 名前を付けて保存しておくことができます Let's Try それでは これを試してみましょう ここでは Access 003 データベースの Northwind サン プル データベース Northwind.mdb に対して リンク サーバーを作成する手順を試してみ ましょう. リンク サーバーを作成するには まず Management Studio のオブジェクト エクスプロー ラで サーバー オブジェクト を展開し リンク サーバー フォルダを右クリックして 新 しいリンク サーバー をクリックします これにより 新しいリンク サーバー ダ ゕログが表示されるので リンク サーバー へ 任意の名前 今回は NW を入力します 次に サーバーの種類 で その他のデータ ソース を選択して プロバ ダ で 59
Microsoft.Jet 4.0 OLEDB Provider を選択します 製品名 へは 任意の文字 ここ では Access 003 を入力し データソース へ Access データベース.mdb フゔ ル へのパスを入力します ここでは C:\Program Files\Microsoft Office\OFFICE\ SAMPLES\Northwind.mdb 設定後 OK ボタンをクリックすれば リンク サーバーの作成が完了です Note 製品名 や プロバイダ文字列 の設定 リンク サーバーの設定画面の データソースに対する プロバイダ や 製品名 product_name プロバイ ダ文字列 provider_string については SQL Server オンラ ン ブックの Transact-SQL リフゔレンスにあ る sp_addlikedserver で詳しく解説されています プロバ ダ名 や製品名. 作成したリンク サーバーを次のように展開すると Access データベース内のテーブルの一 覧を参照することができます 60
3. 次に 作成したリンク サーバー NW を利用して 社員 テーブルのデータを参照してみ ましょう SELECT * FROM NW...社員 リンクサーバーでは リンクサーバー名.データベース名.スキーマ名.テーブル名 という構 文でテーブルへゕクセスできますが Access の場合は リンクサーバー自体がデータベース なので データベース名を省略しています また Access にはスキーマも存在しないので これも省略して NW...社員 と記述しています このようにリンク サーバー機能を利用すると OPENROWSET 関数のように 毎回プロバ ダを指定したり 接続パスを欠かなくて済むようになるので 大変便利です Note コマンドでリンクサーバーを作成する sp_addlinkedserver リンクサーバーは sp_addlinkedserver システム ストゕド プロシージャを利用して コマンドで作成すること も可能です これは 次のように利用します EXEC master..sp_addlinkedserver @server = N'NW', @srvproduct=n'access 003', @provider=n'microsoft.jet.oledb.4.0', @datasrc=n'c:\program Files\Microsoft Office\OFFICE\SAMPLES\Northwind.mdb' スクリプト生成機能 GUI ベースで作成したリンクサーバーは 次のようにスクリプト生成機能を利用して sp_addlinkedserver を自 動生成できるので 便利です 6 sp_addlinkedserver が自動生成される
リモート SQL Server に対してリンク サーバーを作成する場合 リモートの SQL Server に対してリンク サーバーを作成する場合は 次のように操作します. まず Management Studio で リンク サーバー フォルダを右クリックして 新しいリン ク サーバー をクリックします. 新しいリンク サーバー ダ ゕログが表示されたら サーバーの種類 で SQL Server を選択し リンク サーバー へ接続先の SQL Server のインスタンス名を入力します SQL Server の 名前を記述 3. 次に ページの選択 で セキュリティ をクリックして セキュリテゖ ページを開き 任意の接続方法を選択します 3 6
画面のように ログインの現在のセキュリティ コンテキストを使用する をチェックした場 合は 現在ログ ンしているユーザー情報を利用して リモートの SQL Server へのログ ンを試みるようになります 設定後 OK ボタンをクリックすれば リンク サーバーの作 成が完了です 4. リンクサーバー作成後は 設定した SQL Server 上のデータベースの一覧やテーブルを参照 できるようになります リンクサーバーを利用した クエリ実行 リモート SQL Server の データベースやテーブルを 一覧できる クエリを実行するときの構文は 前述したように リンクサーバー名.データベース名.スキー マ名.テーブル名 と 4 つの名前を指定します 画面は POWER という名前の SQL Server の sampledb データベース dbo スキーマ内の 社員 テーブルをクエリしています 63
STEP 4. テーブル関連の便利な操作 この STEP では データ挿入時の自動採番や DEFAULT 値の設定など テーブ ル関連のよく利用する機能について説明します この STEP では 次のことを学習します 自動的に連続番号を振る : IDENTITY プロパテゖ データの全削除を高速に実行する : TRUNCATE TABLE 列に既定値を設定する : DEFAULT 値 64
4. 自動的に連続番号を振る IDENTITY プロパティ IDENTITY プロパティ IDENTITY プロパテゖは 自動的に連番を振ることができる機能です 構文は 次のとおりです IDENTITY (初期値, 増分) 初期値には 採番を始める最初の値を記述します 増分には 採番する際に増やしていく数を記述 します たとえば IDENTITY (,) と記述した場合は から始まって ずつ増加していく番 号 3 を IDENTITY (50,) と記述した場合は 50 から始まって ずつ増加 していく番号 50 503 505 を作成できます 記述する際に 初期値と増分を省略した 場合には IDENTITY(,) と解釈されます Note Oracle の 順序 Access の オートナンバー IDENTITY プロパテゖは Oracle での 順序 SEQUENCE シーケンス Access での オートナンバー に相当す る機能です Let's Try それでは これを試してみましょう. idtest という名前のテーブルを作成し autonum 列へ IDENTITY プロパテゖを設 定します USE sampledb CREATE TABLE idtest ( autonum int IDENTITY (,),col char(0),col char(0) ) このように IDENTITY プロパテゖは テーブル作成時に列に対して設定します 65
データの挿入. 次に idtest テーブルへデータを何件か追加してみましょう IDENTITY プロパテゖを設 定したテーブルへデータを追加 INSERT するには IDENTITY プロパテゖを設定した列以 外 今回は col と col を指定して値を記述します -- データの INSERT INSERT INTO idtest (col,col) VALUES ('aaa', 'xxx') INSERT INTO idtest (col,col) VALUES ('bbb', 'yyy') INSERT INTO idtest (col,col) VALUES ('ccc', 'zzz') -- データの確認 SELECT * FROM idtest データの追加後 SELECT ステートメントで結果を確認すると autonum 列には 3 と からの連続番号が割り振られていることを確認できます このように IDENTITY プロパテゖを使用すると 自動的に連続番号を振ることができるので便利です ただし IDENTITY プロパテゖを設定できる列は テーブル内で つだけです Note テーブル デザイナで IDENTITY プロパティの設定 IDENTITY プロパテゖは Management Studio の テーブル デザイナ 機能を利用して GUI 操作で設定す ることもできます テーブル デザ ナで IDENTITY プロパテゖを設定するには 次のようにオブジェクト エク スプローラで 設定したいテーブルを右クリックして デザイン をクリックします IDENTITY プロパテゖを 設定したい列を選択 列のプロパテゖ タブ 66 IDENTITY の指定 を はい ID の増分 は 増分 IDENTITY シード は 初期値
テーブル デザ ナが表示されたら IDENTETY プロパテゖを設定したい列を選択し 列のプロパティ タブに ある IDENTITY の指定 で はい を選択して ID の増分 へ増分値 IDENTITY シード へ初期値を設 定します IDENTITY を設定した列へ明示的に値を追加 IDENTITY_INSERT IDENTITY プロパテゖを設定した列 以下 IDENTITY 列 には デフォルトでは明示的に値を 追加することができません 値を追加しようとすると 次のエラーが発生します IDENTITY 列へ 50 という値を追加 しようとしている 値を追加したい場合には 次のように SET ステートメントを使って IDENTITY_INSERT を ON へ設定する必要があります SET IDENTITY_INSERT テーブル名 ON Let's Try それでは これを試してみましょう. まずは SET IDENTITY_INSERT を ON へ設定します SET IDENTITY_INSERT idtest ON. 次に autonum 列 IDENTITY 列 へ 値を指定して INSERT ステートメントを実行 してみます -- データの INSERT INSERT INTO idtest (autonum, col, col) VALUES (50, 'ddd', 'zzz') -- データの確認 SELECT * FROM idtest 67
50 を IDENTITY 列へ追加できたことを確認できます このように IDENTITY_INSERT を ON へ設定すると IDENTITY 列へデータを追加できるようになります 明示的に追加した後の採番 3. 次に SET IDENTITY_INSERT を OFF へ戻して この後に自動採番される値がどうなる かを試してみましょう SET IDENTITY_INSERT idtest OFF INSERT INTO idtest (col, col) VALUES ('eee', 'zzz') SELECT * FROM idtest 結果は 5 が採番されます このように 明示的に値を追加した後は その値を基準に増 分値が割り当てられるようになります 現在の IDENTITY 値を調べる IDENT_CURRENT 関数 4. 現在の IDENTITY 値 現在の最大値 は IDENT_CURRENT というシステム関数を利用 して 調べることができます これは 次のように利用します SELECT IDENT_CURRENT ('idtest') 68
結果は 5 が表示されて idtest テーブルの現在の IDENTITY 値 最大値 を確認で きます したがって この次に採番される値は 5 となります データを削除した場合の ID 値 次に データを削除した場合に IDENTITY 値がどうなるのかを試してみましょう. 次のように idtest テーブルの IDENTITY 列が 50 と 5 のデータを削除します DELETE FROM idtest WHERE autonum >= 50. 次に 新しいデータを追加して IDENTITY 値を確認します INSERT INTO idtest (col,col) VALUES ('fff', 'zzz') SELECT * FROM idtest 新しく INSERT したデータには 5 が採番されています このように DELETE ステー トメントでデータを削除しても IDENTITY 値には影響がありません 69
IDENTITY 値を変更する DBCC CHECKIDENT 現在の IDENTITY 値 現在の最大値 を変更したい場合には DBCC CHECKIDENT コマンド を利用します 構文は 次のとおりです DBCC CHECKIDENT ('テーブル名', RESEED, 変更後の任意の IDENTITY 値) RESEED と指定することで IDENTITY 値を変更することができます それでは これを試してみましょう. idtest テーブルの IDENTITY 値が 4 から始まるように 現在の IDENTITY 値を 3 へ変更してみます DBCC CHECKIDENT ('idtest', RESEED, 3) 現在の IDENTITY 値 を 3 に変更 結果は 現在の列値が ３ と表示されて IDENTITY 値を ３ へ変更できたことを確認 できます. 次に データを INSERT してみましょう INSERT INTO idtest (col, col) VALUES ('ggg', 'zzz') SELECT * FROM idtest 追加したデータの IDENTITY 値が 4 になっていることを確認できます 70
Note PRIMARY KEY 制約違反に注意 DBCC CHECKIDENT で IDENTITY を変更した場合は 重複値が PRIMARY KEY 制約違反になることに注意す る必要があります たとえば autonum 列の値が 5 6 7 と増えていって 5 になったとき 5 の データは既に存在しているので ここで重複値が発生します このとき PRIMARY KEY 制約を設定していない場 合には そのまま重複値を格納することができますが PRIMARY KEY 制約を設定している場合には 制約違反で エラーが発生してしまいます なお エラーが発生した後に もう一度データの INSERT を実行した場合には 53 が採番されます 制約違反 エラーが発生しても内部的な採番は行われています Note アプリケーションから IDENTITY 値を取得 SCOPE_IDENTITY 関数 追加された IDENTITY 値をゕプリケーションから取得する場合は 他のユーザーが追加した IDENTITY 値を間 違って取得しないように注意する必要があります 前述の IDENT_CURRENT はあくまでもテーブル全体として の最新値で 自分が追加した値だという保証がないからです したがって これを解決してくれる 自分が追加し た値を保証してくれる のが SCOPE_IDENTITY というシステム関数です ゕプリケーションから IDENTITY 値を取得するには SCOPE_IDENTITY 関数を利用するようにします この関数については本自習書シリーズの 開 発者のための Transact-SQL 応用 で詳しく説明しています 7
4. データの全削除を高速に実行する TRUNCATE TABLE TRUNCATE TABLE テーブル内のすべてのデータを削除するには DELETE ステートメントの WHERE 句を省略する とことで実行することができますが TRUNCATE TABLE というステートメントを利用すると DELETE ステートメントよりも高速にデータを削除できるようになります DELETE ステートメ ントは 削除する 行ごと にトランザクション ログへの記録を行いますが TRUNCATE TABLE ステートメントの場合は トランザクション ログへの記録を 行ごと には行わない ページ単 位で行う からです TRUNCATE TABLE は 次のように利用します TRUNCATE TABLE テーブル名 Let's Try それでは これを試してみましょう. TRUNCATE TABLE ステートメントを利用して idtest テーブルのデータを全削除してみ ましょう TRUNCATE TABLE idtest コマンドは正常に完了しました と表示されれば 削除が完了しています. 次に データが削除されたことを確認しておきましょう SELECT * FROM idtest Note TRUNCATE TABLE 後の IDENTITY 値を取得 DELETE ステートメントでデータを削除した場合は IDENTITY 値に変化はありませんでしたが TRUNCATE TABLE ステートメントでデータを削除した場合は IDENTITY 値は 初期値にリセット RESEED されます したがって IDENTITY(, ) と定義している場合には IDENTITY 値は再び から始まることになります 7
4.3 列に既定値を設定する DEFAULT 値 DEFAULT 値 既定値 DEFAULT 値 既定値 は その名のとおり INSERT 時に値を指定しなかった場合 または 省 略された場合に自動的に補われる値のことです これは 次のように利用します CREATE TABLE テーブル名 ( 列名 データ型 DEFAULT 値, ) Let's Try それでは これを試してみましょう. 次のように deftest という名前のテーブルを作成して col 列へ DEFAULT 値とし て AAA を設定します CREATE TABLE deftest ( autonum int IDENTITY (,),col char(0),col char(0) DEFAULT 'AAA' ). 次に deftest テーブルに対して データを追加します DEFAULT 値を設定した col２ 列へは 値を指定せずにデータを追加します -- データの INSERT INTO INSERT INTO INSERT INTO INSERT deftest (col) VALUES ('xxx') deftest (col) VALUES ('yyy') deftest (col) VALUES ('zzz') -- データの確認 SELECT * FROM deftest 73
col 列には 自動的に AAA が追加されていることを確認できます Note テーブル デザイナで DEFAULT 値を設定 GUI 操作で DEFAULT 値を設定したい場合は 次のように Management Studio のテーブル デザ ナを操作し ます IDENTITY プロパテゖを 設定したい列を選択 既定値またはバ ンド に DEFAULT 値を設定 DEFAULT 値を設定したい列を選択し 列のプロパテゖ タブで 既定値またはバ ンド へ設定したい値を入力 します 既存のテーブルへ DEFAULT 値を設定する 既存のテーブルに対して DEFAULT 値を設定したい場合は ALTER TABLE ステートメントを利 用します それでは これを試してみましょう. deftest テーブルへ DEFAULT 値を設定した col3 列を追加してみます 次のように 記述します ALTER TABLE deftest ADD col3 datetime DEFAULT GETDATE() 74
col3 列の追加時に DEFAU LT 値を設定 DEFAULT 値には GETDATE 関数のよう に 関数を指定することができる col3 列を datetime 型で追加し DEFAULT 値へ GETDATE 関数 現在の日時を取得 できる関数 を指定しています. 次に deftest テーブルに対して データを追加します DEFAULT 値を設定した col3 列には データを追加しないようにしてみます INSERT INTO deftest (col) VALUES ('www') SELECT * FROM deftest 既存のデータは NULL 値 既存データ に対してDEFAULT 値を適用したい場合 は WITH VALUES を使用する GETDATE 関数によって データを追加 したときの時刻が格納される col3 列へ自動的に 現在の日時が格納されていることを確認できます このように DEFAULT 値には関数を指定することもできます Note 後から DEFAULT 値を追加した場合のデータは NULL 値 後から DEFAULT 値を追加した場合は 既存のデータの NULL 値に対しては DEFAULT 値は適用されず NULL 値のままです 既存のデータの NULL 値に対しても DEFAULT 値を適用したい場合には DEFAULT GETDATE() WITH VALUES という形で WITH VALUES を利用するようにします 3. 次に 列の追加時ではなく 既にある col 列に対して DEFAULT 値を BBB へ設定し てみましょう 次のように入力します ALTER TABLE deftest ADD CONSTRAINT def_col DEFAULT 'BBB' FOR col 既存の列に対して DEFAULT 値を設定するには CNSTRAINT.. FOR を利用します 75
4. 次に deftest テーブルに対して データを追加します テーブル内のすべての列が値を省略できる場合 (DEFAULT 値や IDENTITY NULL の許可が設定されている場合 ) は 次のように DEFAULT VALUES を指定して INSERT ステートメントを記述することができます INSERT INTO deftest DEFAULT VALUES 5. 最後に 追加したデータを確認しておきましょう SELECT * FROM deftest col 列へ BBB が追加されたことを確認できます Goal! 以上ですべての操作が完了です 最後までこの自習書の内容を試された皆さま いかがでしたでしょうか? 今回は データのコピーと現場で役立つ操作集 ということで 筆者自身がよく利用する役立つ機能を紹介しました 中でも Integration Services は大変便利なので ぜひ活用していただければと思います Integration Services については 本自習書シリーズの Integration Services 入門 でさらに詳しく説明しているので チャレンジしてみてください Transact-SQL には まだまだ役立つ機能がありますので それらについては 本自習書シリーズの Transact-SQL 入門 と 開発者のための Transact-SQL 応用 を参考にしていただければと思います 76
執筆者プロフィール 有限会社エスキューエル クオリティ http://www.sqlquality.com/ SQL Server と.NET を中心とした コンサルテゖング サービス と メンタリング サービス を提供 主なコンサルテゖング実績 9 TB データベースの物理 論理設計支援 パーテゖショニング対応など 秒あたり,000 Batch Request の ASP ゕプリケーション サービス プロバ ダ サ トの チューニング ピーク時の CPU 利用率 00% を 0% まで軽減 高負荷テスト ラッシュテスト 実施のためのテスト ゕプリの作成支援 大手流通系システムの夜間バッチ実行時間を 4 時間から 時間半へ短縮 大手 ンターネット通販システムの夜間バッチ実行時間を 5 時間から 時間半へ短縮 宅配便トラッキング情報の日中バッチ実行時間を 時間から 5 分へ短縮 検索系 Web サ トのチューニング 0 倍以上のパフォーマンス UP を実現 0 Server によるレプリケーション環境のチューニング 3 TB のセキュリテゖ監査ゕプリケーションのチューニング 大手家電メーカーの制御系ゕプリケーション 00GB のチューニングと運用管理設計 約 3,000 本のストゕド プロシージャとユーザー定義関数のチューニング ASP.NET / ASP Active Server Pages ゕプリケーションのチューニング 大手ゕミューズメント企業の BI システム設計支援 外資系医療メーカーの Analysis Services による 販売分析 システムの設計支援 大手企業の Analysis Services による 財務諸表分析 システムの設計支援 Analysis Services OLAP キューブのパフォーマンス チューニング etc 松本美穂 まつもと みほ 有限会社エスキューエル クオリテゖ 代表取締役 Microsoft MVP for SQL Server / PASSJ 理事 MCDBA Microsoft Certified Database Administrator MCSD for.net Microsoft Certified Solution Developer 現在 SQL Server を中心とするコンサルテゖング 企業に対するメンタリング サービスなどを行っている 今までに手 がけたコンサルテゖング案件は テラバ ト クラスの DB から少人数向け小規模 DB までと 幅広く多岐に渡る 得意分 野はパフォーマンス チューニング コンサルテゖング業務の傍ら 講演や執筆も行い Microsoft 主催の最大 ベント Tech Ed や PASSJ が主催するカンフゔレンスなどでスピーカーとしても活躍中 著書の SQL Server 000 でいっ てみよう と ASP.NET でいってみよう いずれも翔泳社刊 はトップ セラー 前者は 8,500 部 後者は 5,500 部 発行 のびのびになっている SQL Server の新書籍は もうじき刊行予定 松本崇博 まつもと たかひろ 有限会社エスキューエル クオリテゖ 取締役 Microsoft MVP for SQL Server / PASSJ 理事 MCDBA Microsoft Certified Database Administrator MCSD for.net Microsoft Certified Solution Developer SQL Server のパフォーマンス チューニングを得意とするコンサルタント 過去には 約 3,000 本のストゕド プロシー ジャのチューニングや テラバ ト級データベースの論理 物理設計 運用管理設計 高可用性設計などを行う また 過 去には 実際のゕプリケーション開発経験 ASP ASP.NET VB 6.0 Java Access VBA など と システム管理者 IT Pro 経験もあり SQL Server だけでなく ゕプリケーションや OS Web サーバーを絡めた 総合的なコンサルテゖン グが行えるのが強み 最近は Analysis Services と Excel 007 による BI ビジネス ンテリジェンス システムも 得意とする 執筆時のペンネームは 百田昌馬 月刊 Windows Developer マガジンの SQL Server でど んといっ てみよう DB マガジンの SQL Server トラの穴 を連載 マ クロソフト公開のホワ トペーパー 技術文書 の ゴースト ラ ター として活動 すること もあり 過 去 マ ク ロソフト認定 トレーナー 時代には SMS Systems Management Server や Proxy Server Commerce Server BizTalk Server Application Center Outlook CDO な どの講習会も担当 998 年度には Microsoft CTEC 現 CPLS トレーナー ゕワード Trainer of the Year を受賞 77