SQL Server 2012 自習書シリーズ No.3
データのコピーと現場で役立つ便利な操作集
Published: 2008 年 4 月 30 日 SQL Server 2012 更新版: 2012 年 8 月 17 日 有限会社エスキューエル・クオリティ
2
この文章に含まれる情報は、公表の日付の時点での Microsoft Corporation の考え方を表しています。市場の変化に応える必要 があるため、Microsoft は記載されている内容を約束しているわけではありません。この文書の内容は印刷後も正しいとは保障で きません。この文章は情報の提供のみを目的としています。
Microsoft、SQL Server、Visual Studio、Windows、Windows XP、Windows Server、Windows Vista は Microsoft Corporation の米国およびその他の国における登録商標です。
その他、記載されている会社名および製品名は、各社の商標または登録商標です。 © Copyright 2012 Microsoft Corporation. All rights reserved.
目次
STEP 1. 自習書を試す環境について ... 4 1.1 この自習書の内容について ... 5 1.2 自習書を試す環境について ... 6 1.3 事前作業（サンプル スクリプトのダウンロードとセットアップ） ... 7 STEP 2. データのコピーとエクスポート ... 9 2.1 テーブルをコピーする： SELECT .. INTO ... 10 2.2 データをコピーする： INSERT .. SELECT ... 15 2.3 クエリ エディターで CSV 形式の結果を出力する ... 17 2.4 bcp コマンドで CSV ファイル出力（エクスポート） ... 20 2.5 bcp コマンドで CSV ファイル入力（インポート） ... 23STEP 3. Integration Services による エクスポートとインポート ... 25
3.1 Integration Services の概要 ... 26 3.2 Integration Services による CSV ファイルへのエクスポート ... 29 3.3 Integration Services による CSV ファイルのインポート ... 35 3.4 Access データベース（.accdb／.mdb）のインポート ... 44 3.5 OPENROWSET による外部データのクエリ ... 55 3.6 OPENROWSET .. BULK によるテキスト ファイルのインポート ... 61 3.7 リンク サーバーによる外部データのクエリ ... 64 STEP 4. テーブル関連の便利な操作 ... 70 4.1 自動的に連続番号を振る： IDENTITY プロパティ ... 71 4.2 データの全削除を高速に実行する： TRUNCATE TABLE... 78 4.3 列に既定値を設定する： DEFAULT 値 ... 79
4
STEP 1. 自習書を試す環境について
この STEP では、この自習書で取り扱う内容と、自習書を試す環境について説明 します。 この STEP では、次のことを学習します。 この自習書の内容について 自習書を試す環境について 事前作業（サンプル スクリプトのダウンロードとセットアップ）1.1 この自習書の内容について
この自習書の内容について
この自習書では、データやテーブルを単純にコピーする方法や、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 間でデータベースを移動するシナリオ（開発機から本番機、あるいはその逆へ データベースを移動する方法）については、本自習書シリーズの「バックアップと復元」編で、詳 しく説明していますので、こちらもぜひご覧いただければと思います。
6
1.2 自習書を試す環境について
必要な環境
この自習書で実習を行うために必要な環境は次のとおりです。 OS Windows Server 2008 SP2 以降 または Windows Server 2008 R2 SP1 以降 または Windows Server 2012 またはWindows Vista SP2 以降 または Windows 7 SP1 以降 または Windows 8
ソフトウェア
SQL Server 2012
この自習書内での画面やテキストは、OS に Windows Server 2008 R2（x64）、ソフトウェ アに SQL Server 2012 Enterprise エディション（x64）を利用して記述しています。
そのほか
この自習書を試すには、サンプル スクリプトをダウンロードして、次のページの事前作業を実 行しておく必要があります。
1.3 事前作業（サンプル スクリプトのダウンロードとセットアップ）
事前作業
この自習書を進めるには、サンプル スクリプトをダウンロードしておく必要があります。また、 Management Studio の クエリ エディタ ー を利 用して、サンプル スクリプト内にある 「CreateTables.txt」を実行し、「sampleDB」データベースと「社員」テーブルを作成してお く必要があります（実行手順は、次のとおりです）。 1. まずは、Management Studio を起動するために、［スタート］メニューの［すべてのプログ ラム］から、［Microsoft SQL Server 2012］を選択して、［SQL Server Management Studio］をクリックします。 2. 起動後、次のように［サーバーへの接続］ダイアログが表示されたら、［サーバー名］へ SQL Server の名前を入力し、［接続］ボタンをクリックします。 3. 接続完了後、Management Studio が開いたら、次のようにツールバーの［新しいクエリ］ SQL Server の名前を入力 [接続] ボタンをクリック 1 28 4. 次に、Windows エクスプローラーを起動して、サンプル スクリプトをダウンロードしたフ ォルダーを展開し、このフォルダー内の「CreateTables.txt」ファイルをダブル クリックし て開きます。ファイルの内容をすべてコピーして、クエリ エディターへ貼り付けます。 貼り付け後、ツールバーの［！実行］ボタンをクリックしてクエリを実行します。これにより、 「sampleDB」という名前のデータベースが作成され、その中へ「社員」テーブルが作成さ れます。実行後、「社員」テーブルの 6 件のデータが表示されれば、実行が完了です。 「新しいクエリ」をクリック 1 クエリ エディター が表示される 2 サンプル スクリプト内の 「CreateTables.txt」 ファイルの内容をコピー して貼り付け 1 結果を確認 3
STEP 2. データのコピーとエクスポート
この STEP では、SELECT ステートメントで取得した結果をもとに、新しくテー ブルを作成したり、別のテーブルへコピーしたり、CSV ファイル形式へエクスポ ートする方法について説明します。 この STEP では、次のことを学習します。 テーブルをコピーする： SELECT .. INTO データをコピーする： INSERT .. SELECT クエリ エディターで CSV 形式の結果を出力する bcp コマンドで CSV ファイルへの出力（エクスポート） bcp コマンドで CSV ファイルからの入力（インポート）10
2.1 テーブルをコピーする： SELECT .. INTO
SELECT .. INTO
SQL Server では、SELECT .. INTO（SELECT ステートメントで INTO 句）を利用すると、 SELECT ステートメントで取得した結果をもとにテーブルを作成することができます。これは、次 のように記述します。
SELECT * INTO 新しいテーブル名 FROM テーブル名
Let's Try
それでは、これを試してみましょう。
1. まずは、［スタート］メニューの［すべてのプログラム］から［Microsoft SQL Server 2012］ の［SQL Server Management Studio］をクリックして、Management Studio を起動し ます。
2. ［サーバーへの接続］ダイアログでは、［サーバー名］へ SQL Server の名前を入力します。
1
［認証］では、「Windows 認証」を選択して、［接続］ボタンをクリックします（認証につ いては、本自習書シリーズの「ログイン認証とオブジェクト権限」で詳しく説明していますの で、こちらもぜひご覧いただければと思います）。 3. Management Studio が起動したら、次のようにツールバーの［新しいクエリ］ボタンをク リックして、［クエリ エディター］を開きます。 4. クエリ エディターでは、次のように入力して、sampleDB データベースへ接続し、「社員」 テーブルのデータを参照します。 USE sampleDB SELECT * FROM 社員 「社員」テーブルのデータ（6 件）を取得できたことを確認できます。 5. 次に、SELECT .. INTO を利用して、「社員」テーブルのデータをもとに、新しく「社員コピ ー」テーブルを作成してみましょう。 USE sampleDB 「新しいクエリ」をクリック 1 クエリ エディター が表示される SQL を記述 1 ［!実行］をクリック 2 社員テーブルの 中身が表示される 3 sampleDB データベースへ接続 社員テーブルを検索
12 「6 行処理されました」と表示されれば、正しくデータのコピーが完了しています。このよう に、通常の SELECT ステートメントへ "INTO 新しいテーブル名" を追加するだけで、 SELECT ステートメントで取得した結果をもとに INTO 句で指定したテーブルを作成でき るようになります。 6. コピーした「社員コピー」テーブルのデータを確認しておきましょう。 SELECT * FROM 社員コピー 「社員」テーブルのデータが、すべてコピーされていることを確認できます。 7. 次に、作成した「社員コピー」テーブルの定義を確認してみましょう。オブジェクト エクス プローラーで sampleDB データベースの［テーブル］フォルダーを展開して、「社員コピー」 テーブルが表示されることを確認します（表示されない場合は、sampleDB データベースの ［テーブル］フォルダーを右クリックして、［最新の情報に更新］をクリックします）。 「社員コピー」テーブルが表示されたら、これを展開して、列名やデータ型、NULL を許可す るかどうかなどの定義が、コピー元のテーブルと同じであることを確認できます。 1
Note： SELECT .. INTO では制約はコピーされない
SELECT .. INTO では、制約（主キー制約や外部キー制約、CHECK 制約など）をコピーすることはできません。 制約をコピーしたい場合には、別途、次のようにスクリプト生成機能などを利用して、コピー先で、制約を再作成 する必要があります。 ■ 制約のスクリプト生成 制約のスクリプトを生成するには、次のようにテーブルを右クリックして、［テーブルをスクリプト化］から ［CREATE］→「新しいクエリ エディター ウィンドウ」をクリックします。 これにより、テーブルを作成するためのスクリプト（CREATE TABLE）が生成されて、合わせて、制約を作成す るためのスクリプト（ALTER TABLE .. CONSTRAINT）も生成されます。
なお、主キー制約は、次のように CREATE TABLE ステートメントの中へ生成されています。
1
主キー制約（PRIMARY KEY）
14
データをコピーせずにテーブル定義のみをコピーする
次に、SELECT .. INTO を利用して、テーブル定義のみをコピーしてみましょう。 1. 前の手順と同じように SELECT .. INTO を利用して、「社員」テーブルのテーブル定義のみ をコピーした「社員コピー2」という名前の新しいテーブルを作成してみましょう。このとき、 WHERE 句の条件式へ「社員番号 = -1」を付けて実行するようにします。SELECT * INTO 社員コピー2 FROM 社員
WHERE 社員番号 = -1 「0 行処理されました」と表示されるのがポイントです。「社員」テーブルの中には、「社員 番号」が "-1" のデータは存在しないので、0 件と表示されています。これにより、データ は、1 件もコピーされずに、テーブルの定義だけをコピーして、「社員コピー2」テーブルを作 成することができています。このように、WHERE 句の条件式で、結果が ０件になるように 記述すると、テーブル定義だけをコピーできるようになります。 なお、WHERE 句の条件式へは、"WHERE 1=2" のように、成り立たない条件式を記述した 場合にも、結果が 0 件になるので、テーブル定義のみをコピーする目的として利用すること ができます。
2.2 データをコピーする： INSERT .. SELECT
INSERT .. SELECT
前述の SELECT .. INTOは、データのコピーとともに、新しくテーブルを作成するので、既に存在 するテーブルに対してデータをコピーする目的としては利用できません。既存のテーブルへデータ をコピーしたい場合には、INSERT .. SELECT を利用します。これは、次のように利用します。 INSERT INTO テーブル名 SELECT ステートメントこれにより、SELECT ステートメントで取得した結果を INSERT INTO で指定したテーブルへコ ピーできるようになります。
Let's Try
それでは、これを試してみましょう。 1. まずは、「sampleDB」データベース内へ、次のように「emp」という名前のテーブルを作 成します。 USE sampleDBCREATE TABLE emp
( empNo int
,empName char(50) )
2. 次に、INSERT .. SELECT を利用して、「社員」テーブルの「社員番号」と「氏名」列を、「emp」 テーブルの「empNo」と「empName」列へ、それぞれコピーしてみましょう。
INSERT INTO emp
16
既存の「emp」テーブルへ、「社員」テーブルの「社員番号」と「氏名」列のデータをコピー できたことを確認できます。このように INSERT .. SELECT を利用すると、既存のテーブ ルへデータをコピーできるようになります。
Note： INSERT 先のテーブルの列数と SELECT ステートメントで取得する列数を同じにする
INSERT .. SELECT では、INSERT 先のテーブルの列数と SELECT ステートメントで取得する列数が異なる場合 には、エラーが発生します。たとえば、次のように列数が異なる場合は、エラーが発生して、データのコピーに失 敗します。
2.3 クエリ エディターで CSV 形式の結果を出力する
クエリ エディターで CSV 形式の結果を出力
Management Studio の「クエリ エディター」では、SELECT ステートメントで取得した結果を
CSV 形式（カンマ区切りのテキスト形式）へ変換して出力することができます。
Let's Try
それでは、これを試してみましょう。 1. まずは、次のようにクエリ エディター上の任意の領域を右クリックして、［結果の出力］の［結 果をテキストで表示］をクリックします。 これで、グリッド形式ではなく、テキスト形式でクエリ結果を取得できるようになります。 2. 続いて、もう一度、クエリ エディター上の任意の領域を右クリックして、［クエリ オプショ ン］をクリックします。 2 右クリック 1 2 右クリック 118 3. これにより、［クエリ オプション］ダイアログが表示されるので、左ペインで［結果］を展開 して［テキスト］をクリックします。 次に、右ペインの［出力形式］で［コンマ区切り］を選択して、［OK］ボタンをクリックしま す。以上で、設定が完了です。 4. それでは、「社員」テーブルのデータを参照してみましょう。 USE sampleDB SELECT * FROM 社員 結果が、カンマ区切りの CSV 形式で出力されたことを確認できます。このように、クエリ エ ディターの出力形式を「コンマ区切り」へ設定すると、SELECT ステートメントの実行結果 を、CSV 形式で取得できるようになります。
Note： char 型の余分なスペースをトルには RTRIM 関数
上の手順では「氏名」列のデータの右側へ余分なスペースが含まれています。これは、「氏名」列のデータ型を char(50) で定義しているためです。この右側の余分なスペースを取り除くには「RTRIM」という関数を次のよ うに利用します。 ［テキスト］を選択 1 ［出力形式］で［コンマ区切り］ を選択。デフォルトは［固定列］ 2 3
RTRIM 関数については、本自習書シリーズの「Transact-SQL 入門」で詳しく説明しています。 5. 最後に、デフォルトのグリッド形式で結果が出力されるように戻しておきましょう。グリッド 形式へ戻すには、次のようにクエリ エディター上の任意の領域を右クリックして、［結果の出 力］の［結果をグリッドに表示］をクリックします。 2 右クリック 1
20
2.4 bcp コマンドで CSV ファイル出力（エクスポート）
bcp コマンドで CSV ファイル出力（エクスポート）
SQL Server のデータを、CSV 形式のファイルへ出力するには、bcp コマンドを利用することも できます。bcp コマンドの構文は、次のとおりです。 bcp テーブル名 out CSVファイル名 /S SQL Server名 /T /c /t "," 指定しているオプションの役割は、次のとおりです。 /S で SQL Server の名前を指定し、/T で Windows 認証での接続、/c でテキスト形式での出 力、/t "," でカンマ区切りを指定できます。/T と /t は、異なるオプションなので（大文字と小 文字が区別されるので）、注意してください。 bcp コマンドは、「コマンド プロンプト」ツールから実行する必要があります。コマンド プロン プトは、次のように［スタート］メニューの［すべてのプログラム］→［アクセサリ］から、［コ マンド プロンプト］をクリックして、起動することができます。 オプション 役割 /S SQL Server の名前。既定のインスタンスの場合はこのオプションを省略可能 /T Windows 認証で SQL Server へ接続 /c テキスト形式で出力 /t 列区切りを示す記号を指定。”,”と記述した場合はカンマ区切り、省略時は Tab 区切り 1Let's Try
それでは、これを試してみましょう。
1. まずは、［スタート］メニューからコマンド プロンプトを起動します。
2. コマンド プロンプトが起動したら、次のように bcp コマンドを入力して、「社員」テーブル
のデータを「C:\shain.txt」ファイル（CSV 形式）へ出力してみましょう。
bcp sampleDB.dbo.社員 out C:\shain.txt /S Server1 /T /c /t ","
/S オプションに指定した "Server1" は、SQL Server の名前（インスタンス名）になるの で、皆さんの環境に合わせて変更してください。また、テーブル名の指定は "データベース名. スキーマ名.テーブル名" と記述する必要があることに注意してください（スキーマについて は、本自習書シリーズの「ログイン認証とオブジェクト権限」で詳しく説明しています。 コマンドを実行後、"コピーを開始しています..." と表示されて、その後 "n 行コピーされま した" と表示されれば成功です。 Note： エラー「ホストのデータ ファイルが開けません」が出る場合は NTFS アクセス許可を付与 bcp のコマンドの実行時に、次のように「ホストのデータ ファイルが開けません」エラーが発生する場合は、NTFS の書き込み権限がない場合に発生します。 NTFS アクセス許可を付与するか、別途アクセス許可のあるフォルダーを作成して（C:\temp など）、そこへ出力 するようにしてみてください。 3. 次に、Windows エクスプローラーを起動して、「C:\shain.txt」ファイルが作成されてい ることを確認します。 bcp コマンドを入力後 ［ENTER］キーを押す。 1
22
CSV 形式でファイルが作成されていることを確認できます。
Note： bcp は何の略？
bcp は、Bulk Copy の略です。Bulk は、「一括／大量／バラ積み」という意味なので、bcp は "一括コピー プロ グラム" と呼ばれることもあります。 メモ帳で「C:¥shain.txt」ファイル を開いて、結果を確認。「氏名」列 は char 型なので余分な空白あり 2 ダブル クリック 1
2.5 bcp コマンドで CSV ファイル入力（インポート）
bcp コマンドで CSV ファイル入力（インポート）
bcp コマンドでは、テキスト ファイルをインポートする機能もあります。これは、次のように利 用します。 bcp テーブル名 in CSVファイル名 /S SQL Server 名 /T /c /t ", " 出力するときとの違いは、"out" を "in" へ変更しただけです。Let's Try
それでは、これを試してみましょう。 1. 前の step で出力した「C:\shain.txt」を、Step 2.１で作成した「社員コピー２」テーブル へインポートしてみましょう。コマンド プロンプトを起動して、次のように入力します。bcp sampleDB.dbo.社員コピー2 in C:\shain.txt /S Server1 /T /c /t ","
コマンドの実行後、"n 行コピーされました" と表示されれば成功です。
2. 次に、クエリ エディターを開いて、「社員コピー2」テーブルを参照して、データが正しくイ
ンポートされているかどうかを確認しておきましょう。
USE sampleDB
24 Note： bcp コマンドの注意点 bcp コマンドは、単純なデータのコピー用途としては便利ですが、複雑な処理を行うのには適していません。たと えば、インポート元の CSV ファイルの列数とテーブルの列数が異なる場合や、データにカンマが含まれている場 合には、"フォーマット ファイル" を作成しなければなりません（データにカンマが含まれている場合は、そのカ ンマが区切り文字として見なされないようにデータを二重引用符で囲むなどの対処が必要になります）。これらの 問題は、次の Step で説明する Integration Services 機能を利用すると簡単に解決することができます。
STEP 3. Integration Services による
エクスポートとインポート
この STEP では、Integration Services のインポート／エクスポート ウィザード を利用したデータのコピーや、OPENROWSET、リンク サーバーを利用した外部 データのクエリ方法について説明します。 この STEP では、次のことを学習します。 Integration Services の概要 CSV ファイルへのエクスポート CSV ファイルのインポート Access データベースのインポート OPENROWSET を利用した外部データのクエリ リンク サーバーを利用した外部データのクエリ26
3.1 Integration Services の概要
Integration Services の概要
SQL Server Integration Services（SSIS：データ統合サービス）は、SQL Server の標準機能と して搭載されている、データの「コピー」や「変換」などが行える "データ転送ツール" です。SQL Server 2000 以前のバージョンでは、DTS（Data Transformation Services：データ変換サービ ス）と呼ばれていました。
Integration Services を利用すると、SQL Server 同士でのデータ転送はもちろん、Oracle や
DB2、Microsoft Office Access、そのほかの ODBC 対応のデータベース、Microsoft Office Excel
ファイル、可変長のテキスト ファイル（カンマ区切り、タブ区切り）、固定長のテキスト ファイ ルなど、さまざまなデータ ソースから SQL Server へデータを取り込んだり、それとは逆に SQL Server からデータを書き出したりすることができます。
前の Step で紹介した bcp コマンドは、テキスト ファイルとの間での単純なデータ コピーだけ しか行えませんが、Integration Services を利用すると、次のように Access や Excel ファイル、 Oracle などの間で、データのコピーが行えます。
Excel データを SQL Server へ取り込む（インポートする）
Oracle データベースを SQL Server へ取り込む
インポート／エクスポート ウィザード
Integration Services では、これらのデータ転送は、「SQL Server インポートおよびエクスポー
ト ウィザード」（以下、インポート／エクスポート ウィザード）という機能によって、ウィザー
28
SSIS デザイナーによるデータ変換
Integration Services には、単純なデータ コピーだけでなく、次のように、データを変換（デー タを加工しながら転送）できる機能もあります。 この機能は、非常に便利で、データ ウェアハウス（DWH：Data Warehouse）を構築する際には、 欠かせないツールとなります。このようなデータ変換は、SQL Server Data Tools（以前のバージョンの Business Intelligence Development Studio）の「SSIS デザイナー」を利用して行えます。SSIS デザイナーを利用す ると、データの転送に加えて、複雑な変換処理を追加したり、次のように転送の前後に行いたい処 理（FTP によるファイル取得やメール送信、SQL の実行など）を追加したりできるようになりま す。 SSIS デザイナーについては、この自習書では説明していませんが、本自習書シリーズの 「Integration Services 入門」で詳しく説明していますので、こちらもぜひご覧いただければ と思います。 以降では、Integration Services の「インポート／エクスポート ウィザード」を利用して、CSV ファイルとやり取りする方法と Access データベースをインポートする方法を説明します。 データを変換して 転送する 「姓」と「名」を 文字列連結して 「氏名」へ変換 「性別コード」の 1 を「男性」 2 を「女性」へ変換 「部門番号」を 「部門名」へ変換 さまざまなタスク を実行可能 FTP でファイルを取得し たり、複数のファイルをま とめて処理する Foreach Loop、メールを送信した りすることもできる SSIS デザイナーの利用例
3.2 Integration Services による CSV ファイルへのエクスポート
インポート／エクスポート ウィザードによるエクスポート
それでは、Integration Services の「インポート／エクスポート ウィザード」を試してみましょ う。ここでは、「sampleDB」データベースの「社員」テーブルのデータを CSV（カンマ区切り） 形式のファイルへエクスポートしてみましょう。 1. インポート／エクスポート ウィザードを起動するには、次のように Management Studio でコピー元となるデータベース（sampleDB）を右クリックして、［タスク］メニューの［デ ータのエクスポート］をクリックします。 2. これにより、インポート／エクスポート ウィザードが起動されるので、［次へ］ボタンをクリ ックして、次へ進みます。 ［データのエクスポート］ をクリック 130
3. 次の［データ ソースの選択］画面では、コピー元となるデータを指定します。
今回は、事前に「sampleDB」データベースを選択してウィザードを起動しているので、［デ
ータベース］に「sampleDB」が選択されていることを確認できます。また、［データ ソー ス］には「SQL Server Native Client 11.0」が選択されていますが、これは、SQL Server
2012 をコピー元にするという意味です。［サーバー名］には SQL Server の名前が選択され ていることを確認して、［次へ］ボタンをクリックします。 4. 次の［変換先の選択］画面では、変換先（エクスポート先）を指定します。 今回は、CSV ファイルへデータをエクスポートするので、［変換先］で「フラット ファイル 1 2 3 ［フラット ファイル変換先］ を選択 1 出力先となるファイルの 名前を入力 2 テキスト修飾子に「”」 （二重引用符）を入力 3 4
変換先」を選択します。［ファイル名］へは、エクスポート先となる任意のファイル名（画面 は C:\shain2.txt）を入力します。また、［テキスト修飾子］に「"」（二重引用符）を入力し て、データを「"」で囲むようにします。設定後、［次へ］ボタンをクリックします。 5. 次の［テーブルのコピーまたはクエリの指定］画面では、［1 つ以上のテーブルまたはビュー からデータをコピーする］が選択されていることを確認して、［次へ］ボタンをクリックしま す。 6. 次の［フラット ファイルの変換先の構成］画面では、コピー元となるテーブルを選択します。 今回は、［変換元テーブルまたはビュー］で、「社員」テーブルを選択します。 また、［列区切り記号］で［コンマ{,}］が選択されていることを確認します。これで「社員」 ［1つ以上のテーブルま たはビューからデータを コピーする］を選択 1 もし、こちらを選択した場合は SELECT ステートメントを記述して、その結果を 変換先へコピーできる。 2 1 2 3
32 ックします。 7. 次の［パッケージの保存および実行］画面では、このデータ転送をすぐに実行するか、後から 実行するために保存するかを選択します。 今回はすぐに実行するので、［すぐに実行する］が選択されていることを確認して、［次へ］ボ タンをクリックします。 8. 最後の［ウィザードの完了］画面で［完了］ボタンをクリックすると、データのエクスポート が開始されます。 9. エクスポートが完了すると、次のように［操作は正常に実行されました］画面が表示されます。 ［状態］が「成功」と表示されていることを確認して、［閉じる］ボタンをクリックします。 1 2 1
Note： エラー「データ ファイルを開けません」が発生する場合は NTFS アクセス許可を付与 ウィザードの実行で、次のように「データ ファイルを開けません」エラーが発生する場合は、NTFS の書き込み 権限がない場合に発生します。 NTFS アクセス許可を付与するか、別途アクセス許可のあるフォルダーを作成して（C:\temp など）、そこへ出力 するようにしてみてください。 2 1 1
34
エクスポートしたデータの確認
1. ウィザードでエクスポートしたデータを確認するには、Windows エクスプローラーを起動 して、「C:\shain2.txt」ファイルをメモ帳で開きます。 それぞれの列がカンマ区切りで表示され、データが「"」（2 重引用符）で囲まれていることを 確認できます。データを " で囲んでおくと、データ内にカンマがあったとしても、それを区 切り記号と間違えないようになります。このように Integration Services を利用すると、CSV ファイルへのエクスポートを、GUI 操 作だけで簡単に行うことができるようになるので、大変便利です。 各列のデータが ” （2重引用符） で囲まれていることを確認。 コピーの完了後、「C:¥shain2.txt」ファ イルをダブル クリックしてメモ帳で開く 1 ↓
3.3 Integration Services による CSV ファイルのインポート
インポート／エクスポート ウィザードでのインポート
インポート／エクスポート ウィザードでは、CSV ファイルを SQL Server 内へインポートする ことも簡単に行うことができます。Let's Try
それでは、これを試してみましょう。前の Step でエクスポートした「C:\shain2.txt」ファイル を SQL Server へインポートしてみましょう。 1. まずは、［スタート］メニューの［すべてのプログラム］→［Microsoft SQL Server 2012］ から［データのインポートおよびエクスポート（32 ビット）または（64 ビット）］をクリッ クします（X64 環境では、(64 ビット) メニューをクリックします）。 2. インポート／エクスポート ウィザードが起動されたら、［次へ］ボタンをクリックします。36 3. 次の［データ ソースの選択］画面では、データのコピー元を選択します。コピー元がテキス ト ファイルの場合は、［データ ソース］で［フラット ファイル ソース］を選択します。 続いて、［ファイル名］の［参照］ボタンをクリックして、コピー元となるファイルを選択し ます。次のように［ファイルの場所］で「ローカル ディスク（C:）」を選択します。 C: ドライブ内のすべてのファイルが表示されたら、「C:\shain2.txt」ファイルを選択して、 ［開く］ボタンをクリックします。 4. ［データ ソースの選択］画面へ戻ったら、次のように［ファイル名］へ「Shain2.txt」ファ イルへのパスが表示されているのを確認します。 1 2 2 1 3
［テキスト修飾子］へは、「"」（2 重引用符）を入力します。 5. 続いて、次のように［列］ページをクリックして開きます。［列］ページでは、テキスト ファ イルのデータ形式を設定することができます。 ［行区切り記号］に "改行" を表す {CR}{LF} が選択され、［列区切り記号］にコンマ {,} が選択されていることを確認します。今回取り込むファイルは、CSV（カンマ区切り）ファイ ルなので、このままの設定で大丈夫です。 ［2～7 行のプレビュー］には、コピー元の CSV ファイルのデータが表示され、先頭データ テキスト修飾子に「”」 （二重引用符）を入力 2 1 1 2 3 4 5
38 す。
6. 次の［変換先の選択］画面では、コピー先を指定します。今回は、SQL Server へインポート
するので、［変換先］で「SQL Server Native Client 11.0」を選択し、［サーバー名］へ SQL Server の名前を入力します。［データベース］へは、インポート先となるデータベースを選択 しますが、ここでは［新規作成］ボタンをクリックして、新しくデータベースを作成します。 7. ［データベースの作成］ダイアログが表示されたら、［名前］へ新しく作成する任意のデータ ベース名（今回は、SSISDB）を入力し、［OK］ボタンをクリックします。これで、SSISDB という名前のデータベースが作成されます。 8. ［変換先の選択］画面へ戻ったら、［次へ］ボタンをクリックして次の画面へ進みます。 1 2 1 2
9. 次の［コピー元のテーブルおよびビューを選択］画面では、インポート元とインポート先とな るテーブルを指定します。今回のようにインポート元がテキスト ファイルの場合には、［変換 元］へファイル パス（C:\shain2.txt）が表示されます。 ［変換先］へは、[dbo].[shain2] と表示されますが、これは「SSISDB」データベース内 へ「shain2」という名前のテーブルを作成するという意味です（テーブルは実際にデータが 転送されるときに自動作成されます）。テーブル名は、ここで変更することもできますが、今 回は、「shain2」という名前のままにしておきます。 10. 続いて、［マッピングの編集］ボタンをクリックします。すると、次のように［列マッピング］ 1 1
40 ます。 既定では、テキスト ファイルのデータは、varchar データ型として設定され、列名は、変換 元と同じ列名が設定されます。 今回は、「入社日」と「部門番号」は、「<無視>」を選択して、コピー対象から外すようにし ます。このダイアログでは、データ型やサイズなども変更できますが、今回は、このままで［OK］ ボタンをクリックします。 11. ［コピー元のテーブルおよびビューを選択］画面へ戻ったら、［次へ］ボタンをクリックしま す。 ［マッピングの編集］をクリックした場 合はコピーしない列を設定したりできる。 CSV ファイルの列数とテーブルの列数が 異なる場合に役立つ 列名やデータ型の 変更も可能 1 2
12. 次の［パッケージの保存および実行］画面では、［すぐに実行する］を選択して、［次へ］ボタ ンをクリックします。 13. 最後の［ウィザードの完了］画面で［完了］ボタンをクリックすると、データのインポートが 開始されます。 1 1 2
42 14. インポートが完了すると、［操作は正常に実行されました］画面が表示されます。［状態］が「成 功」と表示されていることを確認して、［閉じる］ボタンをクリックします。
インポートされたデータの確認
1. ウィザードでインポートしたデータを確認するには、Management Studio のオブジェクト エクスプローラーで［データベース］フォルダーを右クリックして、［最新の情報に更新］を クリックします。「SSISDB」データベースが表示されたら、次のように［テーブル］フォル 1 1ダーを展開して、「shain2」という名前のテーブルを表示します。 2. 続いて、「shain2」テーブルを右クリックして［上位 1000 行の選択］をクリックします。 これにより、shain2 テーブルのデータが表示されます。社員番号と氏名、給与がコピーされ ていて、入社日と部門番号については、コピーされていないことを確認できます。 このように、Integration Services では、インポート元の CSV ファイルの列数とテーブル の列数が異なる場合にも、簡単にコピーすることができます（前の Step で利用した bcp コ マンドで同じことを実現しようとすると、フォーマット ファイルを作成しなければなりませ ん）。 1 1 「shain2」テーブル のデータが表示される 2
44
3.4 Access データベース（.accdb／.mdb）のインポート
Access データベース（.accdb／.mdb）のインポート
Integration Services を利用すると、Access データベース（.accdb／.mdb ファイル）も簡単 にインポートすることができます。
Let's Try
それでは、これを試してみましょう。ここでは、Microsoft Access 2010 に付属の「ノースウィ ンド」サンプル データベースを SQL Server へインポートしてみましょう。ノースウィンド サ ンプル データベースは、販売管理を題材としたサンプル データベースで、次のように商品に関す る受注情報が格納されています。 以降の手順を試すには、このノース ウインド サンプル データベース（ノースウィンド.accdb） が必要になりますが、ほかの .accdb ファイルや Access 2003 以前のデータベース（.mdb フ ァイル）でもほとんど同じように試せるので、ノースウィンド サンプル データベースがない場合 は、任意の .accdb／.mdb ファイルで試してみてください。Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント
ローカル マシン（インポート／エクスポート ウィザードを実行するマシン）に Office 2010 を インストールしておくか、インストールしていない場合は、下記サイトから「Access データベー ス エンジン 2010 再頒布可能コンポーネント」をダウンロードして、インストールしておく必要 があります。 Access データベース エンジン 2010 再頒布可能コンポーネント http://www.microsoft.com/ja-jp/download/details.aspx?id=13255
32-bit 環 境 の 場 合 は 「 AccessDatabaseEngine.exe 」、 X64 （ 64-bit ） 環 境 の 場 合 は 「AccessDatabaseEngine_X64.exe」ファイルをダウンロードしてインストールしておきます。 ダウンロードしたファイル「AccessDatabaseEngine.exe」は、ダブル クリックすれば、次の ようにインストーラーが起動して、インストールすることができます。
46 なお、Access 2003 以前のデータベース「.mdb」をインポートする場合には、上記のコンポーネ ントは不要です（Access 2007 以降のデータベース「.accdb」をインポートする場合に必要）。
インポート／エクスポート ウィザードの起動
1. まずは、次のように［スタート］メニューの［Microsoft SQL Server 2012］から［デー タのインポートおよびエクスポート（32 ビット）または（64 ビット）］をクリックして、ウ ィザードを起動します（X64 環境の場合は、(64 ビット) メニューをクリックします）。 12. インポート／エクスポート ウィザードが起動したら、［次へ］ボタンをクリックします。
3. 次の［データ ソースの選択］画面では、［データ ソース］（コピー元となるデータベース）に
「Microsoft Access（Microsoft Access Database Engine）」を選択します（X64 環境 の場合に (64 ビット) メニューをクリックせずに、(32 ビット) メニューをクリックした場 合は、このデータソースが表示されないので注意してください）。 続いて、［ファイル名］の［参照］ボタンをクリックして、［ファイルを開く］ダイアログを表 1 1 2
48 ［すべてのファイル（*.*）］を選択して、ノースウィンド サンプル データベース（ノース ウィンド.accdb）を選択し、［開く］ボタンをクリックします。 ［データ ソースの選択］画面へ戻ったら、［次へ］ボタンをクリックします。 Note： Access 2003 データベース（.mdb）をインポートしたい場合 Access 2003 以前のデータベース（.mdb ファイル形式のデータベース）をインポートしたい場合にも、［データ ソース］に「Microsoft Access（Microsoft Access Database Engine）」を選択することが可能です。あるい は、［データソース］で［Microsoft Access（Microsoft Jet Database Engine）］（Jet エンジン）を選択して もインポートを行うことが可能です。ただし、Jet は 32 ビット版のみしか提供されていないので、X64 環境の場 合は、（32 ビット）のメニューからインポート／エクスポート ウィザードを起動する必要があります。
4. 次の［変換先の選択］画面では、［変換先］に「SQL Server Native Client 11.0」を選択
して、［サーバー名］に SQL Server の名前（画面は SERVER1）を入力します。 ［データベース］で「SSISDB」を選択して、［次へ］ボタンをクリックします。 2 1 3 1 2 2 3
5. 次の［テーブルのコピーまたはクエリの指定］画面では、［1 つ以上のテーブルまたはビュー からデータをコピーする］を選択して、［次へ］ボタンをクリックします。 6. 次の、［コピー元のテーブルおよびビューを選択］画面では、Access データベース内のテー ブルが一覧されます。 ここでは、「仕入先」や「社員」、「受注」、「受注明細」、「商品」、「得意先」など、任意のテー ブルをチェックして、インポート対象に設定します。設定後、［次へ］ボタンをクリックしま 1 2 1 2
50 7. 次の［データ型マッピングの確認］画面では、テーブル内の各列のマッピング（変換されるデ ータ型）を確認して、［次へ］ボタンをクリックします。 8. 次の［パッケージの保存および実行］画面では、［すぐに実行する］を選択して、［次へ］ボタ ンをクリックします。 9. 最後の［ウィザードの完了］画面で［完了］ボタンをクリックすると、データのコピーが開始 されます。 1 1 2
10. インポートが完了すると、次のように［操作は正常に実行されました］画面が表示されます。
［状態］が「成功」と表示されていることを確認して、［閉じる］ボタンをクリックします。
以上で、Access データのインポートが完了です。
1
52
インポートされたデータの確認
1. インポートしたデータを確認するには、Management Studio のオブジェクト エクスプロ ーラーで「SSISDB」データベースを展開して、［テーブル］フォルダーを右クリックし、［最 新の情報に更新］をクリックします。 インポートしたテーブル（仕入先や社員、受注、受注明細、商品、得意先など）が表示される ことを確認できます。 2. 次に、「社員」テーブルや「商品」テーブルを右クリックして、［上位 1000 行の選択］をク リックし、テーブルの中身を確認しておきましょう。 「社員」テーブルのデータの確認 1 1 2「商品」テーブルのデータの確認
Note： Access 側で設定された主キーやリレーションシップは転送されない
Integration Services では、Access データベース側で設定された主キーやリレーションシップなどの制約はコピ ーされません。Access 側で設定されていたリレーションシップは、次のとおりです。
54 ［キー］フォルダーや［制約］フォルダーを参照しても、制約が作成されていないことから、制約が転送されてい ないことを確認できます。したがって、主キーやリレーションシップなどは、データのインポート後に手動で設定 する必要があります。 社員テーブルの列の一覧。 主キーと外部キーには 鍵のアイコンが付くが、 設定されていない 主キーと外部キーが一覧される場所。 ここに存在しないことからも主キー と外部キーが設定されていないこと を確認できる 制約が一覧される場所 （ココも空）
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', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
また、OPENROWSET による操作が完了した後は、次のようにアドホック クエリを無効化してお くようにしましょう。
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
リモートの SQL Server へ接続する場合
OPENROWSET 関数を利用して、リモートの SQL Server（別マシンにインストールされた SQL Server 2012 や下位バージョンの SQL Server）のデータをクエリする場合には、次のように記 述します。 SELECT * FROM OPENROWSET('SQLNCLI' , 'Server=BAMBOO;Trusted_Connection=yes;'56
第 1 引数の「SQLNCLI」は、SQL Server Native Client 11.0 を利用するという意味で、第 2 引数では、「Server=」に続けて、接続したい SQL Server の名前（画面は BAMBOO）を指定 します。また、「;」（セミコロン）を記述して、「;Trusted_Connection=yes;」と記述すること で、Windows 認証での接続を試みることができます（セミコロンの部分を、カンマにしないよう に注意してください）。第 3 引数には、リモートの SQL Server で実行したい SELELCT ステー トメントを記述します。
このように、OPENROWSET 関数を利用すると、リモート（外部データ）を簡単にクエリするこ とができます。また、OPENROWSET で取得した結果に対して、Step2 で説明した SELECT ..
INTO を利用すれば、結果をもとにテーブルを作成することもできます。
Access データベース（.accdb／.mdb）をクエリする場合
OPENROWSET では、前述の「Access データベース エンジン 2010 再頒布コンポーネント」 をインストールしていれば、Access のデータベース（.accdb／.mdb）をクエリすることもでき ます。これは、次のように利用します（Access 2010 データベースへ接続する場合）。 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' , 'C:\temp\ノースウィンド.accdb';'admin';'' , 'SELECT * FROM 社員') Access データベースへ接続する場合には、第 1 引数へ「Microsoft.ACE.OLEDB 12.0」を指定し、第 2 引数には .accdb または .mdb ファイルへのパスを記述します（ここでは、Access 2010 のノースウィンド サンプル データベースへのパスを記述しています）。パスに続けて、「;」（セミ コロン）を記述し、「;'admin';''」と指定します。これは Access に接続するためのユーザー名と パスワードの指定ですが、デフォルトでは admin／パスワードなしで接続できます。また、セミ コロンの部分は、カンマにしないように注意してください。第 3 引数には、"SELECT * FROM 社 員" のように Access データベースに対して実行したい任意の SELECT ステートメントを記述 できます。 Note： エラー「アクセスが拒否されました」、「列情報を取得できません」が表示される場合 実行時には、次のように「アクセスが拒否されました」および「列情報を取得できません」エラーが発生する場合があり ます。 このエラーは、次のように「AllowInProcess」と「DynamicParameters」プロパティを有効化（1 へ設定）すること で回避できます。 これを実行してもエラーが出る場合は、SQL Server を再起動してみてください。それでもエラーが出る場合は、OS も 再起動してみてください。 なお、上記のプロパティは、オブジェクト エクスプローラーで、次のように［サーバー オブジェクト］の［リンク サー バー］→［プロバイダー］を展開して、「Microsoft.ACE.OLEDB 12.0」のプロパティを開き、「動的パラメーター」と 「InProcess 許可」をチェックしても設定することができます。 1 2 3
58
Note： エラー「データ ソース オブジェクトが初期化できませんでした」が表示される場合
実行時に、次のように「データ ソース オブジェクトが初期化できませんでした」エラーが発生する場合には、SQL Server のサービス アカウントの temp フォルダーに対する NTFS アクセス許可がない場合か、ファイルのパスを間違ってい る場合（存在しないフォルダー名やファイル名を指定している場合）などで発生します。
OPENROWSET 関数は、内部的に、サービス アカウントの temp フォルダー（既定では、C:\Users\<サービス アカ ウント名>\AppData\Local フォルダー） へアクセスするため、このフォルダーに対して、OPENROWSET の実行ユ ーザーがアクセス許可を持っている必要があります。
Note： 32 ビット環境の場合は Jet で Access 2003 以前のデータベース（.mdb）を参照可能
32 ビット環境では、Jet の OLEDB プロバイダーを利用して、Access 2003 以前のデータベース（.mdb ファイル）を クエリすることができます。これは、次のように利用できます。
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'C:\temp\Northwind.mdb';'admin';''
, 'SELECT * FROM 社員')
この Jet プロバイダーは、SQL Server 2012 のインストール時に自動的にインストールされるので、「Access データ ベース エンジン 2010 再頒布コンポーネント」をインストールする必要はありません。ただし、この Jet プロバイダー は、32 ビットのみの提供なので、X64 環境では利用することができません（OPENROWSET は 64 ビットで動作するた め）。 実行時に、「アクセスが拒否されました」および「列情報を取得できません」エラーが発生する場合は、次のように Jet プ ロバイダーに対して「AllowInProcess」と「DynamicParameters」プロパティを有効化することで回避できます。
Excel データ（.xlsx／.xls）をクエリ
OPENROWSET 関数では、Excel データ（.xlsx／.xls ファイル）をクエリすることもできます。 これは、次のように利用します（Excel 2010 データの場合）。 SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0' , 'Excel 12.0;Database=C:\temp\Shohin.xlsx;'Excel 2010 へ接続する場合には、Access 2010 のときと同様、「Access データベース エンジ ン 2010 再頒布コンポーネント」を利用するので、第 1 引数には「Microsoft.ACE.OLEDB.12.0」
と指定します。第 2 引数には、「Excel 12.0;Database=C:\temp\Shohin.xlsx;」のように記 述します。Excel 12.0 と指定することで、Excel データであることを指定し、Database= に続 けて、.xlsx または .xls ファイルへのパスを記述します。第 3 引数には、"SELECT * FROM [Sheet1$]" のように記述し、シート名に「$」マークを付けて、大カッコ [ ] で囲みます。Excel 側で「名前付き範囲」を作成している場合は、＄マークなしで、大カッコ [ ] で囲んで名前付き範 囲を指定することもできます。 このように、OPENROWSET 関数を利用すると、Excel データも簡単にクエリすることができま す。なお、クエリする Excel ファイルが、Excel によって開かれている場合は、エラーになるの で、必ず Excel を終了してから、このクエリを実行するようにします。
Note： 32 ビット環境の場合は Jet で Excel 2003 以前のファイル（.xls）を参照可能
32 ビット環境では、Jet の OLEDB プロバイダーを利用して、Excel 2003 以前のファイル形式（.xls ファイル）をク エリすることができます。これは、次のように記述します。
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=C:\temp\Shohin.xls;'
, 'SELECT * FROM [Sheet1$]')
この Jet の OLEDB プロバイダーは、32 ビットのみの提供なので、X64 環境では利用することができません。
Oracle データベースをクエリ
OPENROWSET 関数では、Oracle データベース（Oracle 9i や 10g、11g など）をクエリする こともできます。これは、次のように利用します（Oracle 11g R2 データベースの場合）。
SELECT * FROM
OPENROWSET('OraOLEDB.Oracle'
, 'ORCL';'SCOTT';'tiger'
, 'SELECT * FROM EMP')
60
Oracle 11g R2 データベースへ接続する場合は、第 1 引数へ「OraOLEDB.Oracle」を指定し、
第 2 引数には、「'ORCL';'SCOTT';'tiger'」のように、ローカル・ネット・サービス名、ユーザー 名、パスワードの順に記述します。第 3 引数には、"SELECT * FROM EMP" のように Oracle Server に対して実行したい任意の SELECT ステートメントを記述できます。なお、Oracle デー タベースへ接続するには、事前に「Oracle Client」ソフトウェアをインストールして、ローカル・ ネット・サービス名を設定しておく必要があります。 このように、OPENROWSET 関数を利用すると、Oracle データベースも簡単にクエリできるよう になります。 Note： エラー「アクセスが拒否されました」、「列情報を取得できません」が表示される場合 実行時には、次のように「アクセスが拒否されました」および「列情報を取得できません」が発生する場合があります。 このエラーは、次のように「AllowInProcess」と「DynamicParameters」プロパティを有効化（1 へ設定）すること で回避できます。 これを実行してもエラーが出る場合は、SQL Server を再起動してみてください。それでもエラーが出る場合は、OS も 再起動してみてください。
3.6 OPENROWSET .. BULK によるテキスト ファイルのインポート
OPENROWSET .. BULK でテキスト ファイルを一括インポート
OPENROWSET 関数では、テキスト ファイルを一括インポートする機能もあります。これは、次 のように利用します。 SELECT * FROM OPENROWSET( BULK 'テキスト ファイル名' ,FORMATFILE = 'フォーマット ファイル名' ) 別名 OPENROWSET での一括インポートでは、フォーマット ファイルを別途作成する必要があります。 このファイルは、bcp コマンドや BULK INSERT というステートメントで利用できるものと同 じ形式なのですが、インポート先の列名や列数と、テキスト ファイル内の列数や区切り文字など を対応付けするためのファイルです。これは、次のような形式をとります。フォーマット ファイルの自動生成： bcp コマンド
bcp コマンドには、既存のテーブルをもとに、フォーマット ファイルを自動生成してくれる機能 があります。たとえば、次のようにインポート先のテーブルがあるとします。 -- インポート先のテーブル CREATE TABLE t1 ( a int ,b char(10 ) ) この場合に、bcp コマンドをコマンド プロンプトから次のように実行すると、フォーマット ファ イルを自動生成してくれます。bcp sampleDB.dbo.t1 format nul /T /c /t "," /f C:\test.fmt
自動生成されたフォーマット ファイル（カンマ区切りの CSV ファイル用）
62 任意で指定できます（拡張子を .fmt とするのが慣習です）。なお、別サーバーや名前付きインス タンスの場合は /S オプションを利用して SQL Server のインスタンス名を指定する必要があり ます（ローカルの既定のインスタンスの場合は /S オプションを省略可能）。また、実行時に、「BCP 形式のファイルを開けません」エラーが発生する場合は、NTFS の書き込み権限がない場合なので、 別途アクセス許可のあるフォルダーを作成して（C:\temp など）、そこへフォーマット ファイル を作成するようにしてください。
OPENROWSET .. BULK での一括インポート
次に、OPENROWSET 関数でテキスト ファイルを一括インポートしてみましょう。次のテキスト ファイル（カンマ区切り）を t1 テーブルへ一括インポートするとします。 このファイルが「C:\bulkTest1.csv」の場合は、OPENROWSET 関数を次のように記述します。 SELECT * FROMOPENROWSET( BULK 'C:\bulkTest1.csv'
,FORMATFILE = 'C:\test.fmt' ) b
これを、次のように INSERT .. SELECT で利用すれば、t1 テーブルへ一括インポートできるよ うになります。
INSERT INTO t1
SELECT * FROM
OPENROWSET( BULK 'C:\bulkTest1.csv'
MERGE ステートメントと OPENROWSET .. BULK の連携
OPENROWSET .. BULK は、SQL Server 2008 から提供された機能の「MERGE」ステートメン トと連携して利用することもできます。MERGE ステートメントは、データが存在する場合には
UPDATE、存在しない場合には INSERT 処理が行える非常に便利なステートメントです（詳し
くは、本自習書シリーズの「開発者のための Transact-SQL 応用」で説明しています）。 したがって、MERGE ステートメントと OPENROWSET .. BULK を組み合わせると、一括インポ ート時に、データが既に存在する場合には UPDATE、存在しない場合には INSERT 処理が行える ようになります。
たとえば、インポートしたいファイルが、次の「C:\bulkTest2.csv」だとします。
この場合は、次のように実行することができます。
MERGE INTO t1
USING OPENROWSET( BULK 'C:\bulkTest2.csv'
,FORMATFILE = 'C:\test.fmt' ) bulk1 ON t1.a = bulk1.a
WHEN MATCHED THEN
UPDATE SET t1.b = bulk1.b
WHEN NOT MATCHED THEN
INSERT VALUES ( bulk1.a, bulk1.b );
このように OPENROWSET 関数は、テキスト ファイルを一括インポートするシナリオでも大変 役立ちます。 t1 テーブル C:¥bulkTest2.csv ファイル UPDATE されたデータ INSERT されたデータ MERGE
