データの一括インポートでのフォーマットファイルの使用 (SQL Server)

[アーティクル]
04/04/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

この記事では、一括インポート操作でのフォーマットファイルの使用方法について説明します。フォーマットファイルでは、データファイルのフィールドがテーブルの列にマップされます。詳細については、「フォーマットファイルの作成 (SQL Server)」を参照してください。

開始する前に

Unicode 文字データファイルを操作するフォーマットファイルの場合、すべての入力フィールドが Unicode テキスト文字列 (つまり、固定サイズの Unicode 文字列または終端文字が指定された Unicode 文字列) でなければなりません。
SQLXML データを一括エクスポートまたは一括インポートする場合、フォーマットファイルのデータ型には次のいずれかを使用します。
- SQLCHAR または SQLVARCHAR (データは、クライアントコードページまたは照合順序で暗黙的に指定されるコードページで送られます)
- SQLNCHAR または SQLNVARCHAR (データは Unicode として送信されます)
- SQLBINARY または SQLVARBIN (データは変換なしで送られます)
Azure SQL Database と Azure Synapse Analytics では、bcp のみサポートされます。追加情報については、次をご覧ください：
一括挿入を含むこの構文は、Azure Synapse Analytics ではサポートされていません。 Azure Synapse Analytics やその他のクラウドデータベースプラットフォーム統合では、Azure Data Factory の COPY ステートメントを介して、または COPY INTO や PolyBase などの T-SQL ステートメントを使用してデータ移動を実行します。

テスト条件の例

このトピックのフォーマットファイルの例は、以下に定義されたテーブルとデータファイルに基づいています。

サンプルテーブル

以下のスクリプトでは、テストデータベースと myFirstImportという名前のテーブルが作成されます。 Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE dbo.MyFirstImport (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );

サンプルデータファイル

メモ帳を使用して、空のファイル D:\BCP\myFirstImport.bcp を作成し、次のデータを挿入します。

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

また、次の PowerShell スクリプトを実行して、データファイルを作成および設定することもできます。

Clear-Host
# revise directory as desired

$dir = 'D:\BCP\';

$bcpFile = Join-Path -Path $dir -ChildPath 'MyFirstImport.bcp';

# Confirm directory exists

IF ((Test-Path -Path $dir) -eq 0)
{
    Write-Host "The path $dir does not exist; please create or modify the directory.";
    RETURN;
};

# Clear content, will error if file does not exist, can be ignored

Clear-Content -Path $bcpFile -ErrorAction SilentlyContinue;

# Add data

Add-Content -Path $bcpFile -Value '1,Anthony,Grosse,1980-02-23';
Add-Content -Path $bcpFile -Value '2,Alica,Fatnowna,1963-11-14';
Add-Content -Path $bcpFile -Value '3,Stella,Rosenhain,1992-03-02';

# Review content

Get-Content -Path $bcpFile;
Notepad.exe $bcpfile;

フォーマットファイルを作成する

SQL Server は、非 XML 形式と XML 形式の 2 種類のフォーマットファイルをサポートしています。 XML 以外のフォーマットとは、以前のバージョンの SQL Server でサポートされる従来のフォーマットです。

XML 以外のフォーマットファイルを作成する

詳細については、「XML 以外のフォーマットファイル (SQL Server)」を参照してください。次のコマンドでは、 bcp ユーティリティを使用し、 myFirstImport.fmtのスキーマに基づいて XML 以外のフォーマットファイル myFirstImportを生成します。 bcp コマンドを使用してフォーマットファイルを作成するには、 format 引数を指定し、データファイルのパスの代わりに nul を使用します。 format オプションには、次に示す -f オプションが必要です。さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用してフィールドターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.fmt

非 XML フォーマットファイルの D:\BCP\myFirstImport.fmt は次のようになります。

13.0
4
1       SQLCHAR             0       7       ","      1     PersonID               ""
2       SQLCHAR             0       25      ","      2     FirstName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       30      ","      3     LastName               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             0       11      "\r\n"   4     BirthDate              ""

重要

XML 以外のフォーマットファイルは、キャリッジリターン\ラインフィードで終わるようにします。そうしないと、次のエラーメッセージが発生する可能性があります。

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file

XML フォーマットファイルを作成する

詳細については、「XML フォーマットファイル (SQL Server)」を参照してください。次のコマンドでは、 bcp ユーティリティを使用し、 myFirstImport.xmlのスキーマに基づいて XML のフォーマットファイル myFirstImportを生成します。 bcp コマンドを使用してフォーマットファイルを作成するには、 format 引数を指定し、データファイルのパスの代わりに nul を使用します。 format オプションには常に -f オプションが必要です。XML フォーマットファイルを作成するには、 -x オプションも指定する必要があります。さらに、この例では、修飾子 c を使用して文字データを指定し、 t を使用してフィールドターミネータとしてコンマを指定し、 T を使用して統合セキュリティによる信頼された接続を指定します。コマンドプロンプトで、次のコマンドを入力します。

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T

REM Review file
Notepad D:\BCP\myFirstImport.xml

XML フォーマットファイルの D:\BCP\myFirstImport.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="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>