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

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

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

[前提条件]

SQL Server インスタンスで T-SQL コマンドを実行するには、 SQL Server Management Studio (SSMS)、 Visual Studio Code 用の MSSQL 拡張機能、 sqlcmd、またはお気に入りの T-SQL クエリツールを使用します。
Unicode 文字データファイルを操作するフォーマットファイルの場合、すべての入力フィールドが Unicode テキスト文字列 (つまり、固定サイズの Unicode 文字列または終端文字が指定された Unicode 文字列) でなければなりません。
XML ドキュメント (SQL Server) データの一括インポートと一括エクスポートの例を一括エクスポートまたはインポートするには、フォーマットファイルで次のいずれかのデータ型を使用します。
- 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 を使用します。
書式オプションには、 -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>