次の方法で共有


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

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

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

[前提条件]

テスト条件の例

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

サンプル テーブル

以下のスクリプトでは、テスト データベースと 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>

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

次の例では、データベース、データ ファイル、および上記で作成したフォーマット ファイルを使用します。

bcpXML 以外のフォーマット ファイルを使用する (SQL Server)

コマンド プロンプトで、次のコマンドを入力します。

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"

bcp および XML フォーマット ファイルを使用する (SQL Server)

コマンド プロンプトで、次のコマンドを入力します。

REM Truncate table (for testing)
SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"

REM Import data
bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T

REM Review results
SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"

BULK INSERT (Transact-SQL)XML 以外のフォーマット ファイルの使用 (SQL Server)

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.fmt');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

BULK INSERT (Transact-SQL) および XML フォーマット ファイルを使用する (SQL Server)

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
BULK INSERT dbo.myFirstImport  
   FROM 'D:\BCP\myFirstImport.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');  
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

OPENROWSET BULK (Transact-SQL)XML 以外のフォーマット ファイルを使用する (SQL Server)

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

USE TestDatabase;
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.fmt'
        ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

OPENROWSET BULK (Transact-SQL) および XML フォーマット ファイルを使用する (SQL Server)

Microsoft SQL Server Management Studio (SSMS) で、次の Transact SQL を実行します。

USE TestDatabase;  
GO

TRUNCATE TABLE myFirstImport; -- (for testing)
INSERT INTO dbo.myFirstImport  
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myFirstImport.bcp',
        FORMATFILE = 'D:\BCP\myFirstImport.xml'  
       ) AS t1;
GO

-- review results
SELECT * FROM TestDatabase.dbo.myFirstImport;

その他の例