Utilizzo di un file di formato per l'importazione bulk dei dati (SQL Server)Use a Format File to Bulk Import Data (SQL Server)

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

In questo argomento viene illustrato l'utilizzo di un file di formato per operazioni di importazione bulk.This topic illustrates the use of a format file in bulk-import operations. Un file di formato esegue il mapping dei campi del file di dati alle colonne della tabella.A format file maps the fields of the data file to the columns of the table. Per altre informazioni, vedere Creazione di un file di formato (SQL Server) .Please review Create a Format File (SQL Server) for additional information.

RiquadroOutline
Prima di iniziareBefore You Begin
Condizioni di test di esempioExample Test Conditions
 ● Tabella di esempio ● Sample Table
 ● File di dati di esempio ● Sample Data File
Creazione dei file di formatoCreating the Format Files
 ● Creazione di un file di formato non XML ● Creating a Non-XML Format File
 ● Creazione di un file di formato XML ● Creating an XML Format File
Utilizzo di un file di formato per l'importazione bulk dei datiUsing a Format File to Bulk Import Data
 ● Uso di bcp e di un file di formato non XML ● Using bcp and Non-XML Format File
 ● Uso di bcp e di un file di formato XML ● Using bcp and XML Format File
 ● Uso di BULK INSERT e di un file di formato non XML ● Using BULK INSERT and Non-XML Format File
 ● Uso di BULK INSERT e di un file di formato XML ● Using BULK INSERT and XML Format File
 ● Uso di OPENROWSET(BULK...) e di un file di formato non XML ● Using OPENROWSET(BULK...) and Non-XML Format File
 ● Uso di OPENROWSET(BULK...) e di un file di formato XML ● Using OPENROWSET(BULK...) and XML Format File

Prima di iniziareBefore You Begin

  • Affinché un file di formato funzioni con un file di dati di formato carattere Unicode, è necessario che tutti i campi di input siano stringhe di testo Unicode, ovvero stringhe Unicode di dimensioni fisse o che terminano con un carattere.For a format file to work with a Unicode character data file, all input fields must be Unicode text strings (that is, either fixed-size or character-terminated Unicode strings).
  • Per l'esportazione o l'importazione in blocchi di dati SQLXML usare uno dei tipi di dati seguenti nel file di formato:To bulk export or import SQLXML data, use one of the following data types in your format file:
    • SQLCHAR o SQLVARYCHAR (i dati vengono inviati nella tabella codici del client o nella tabella codici implicita delle regole di confronto)SQLCHAR or SQLVARYCHAR (the data is sent in the client code page or in the code page implied by the collation)
    • SQLNCHAR or SQLNVARCHAR (i dati vengono inviati come Unicode)SQLNCHAR or SQLNVARCHAR (the data is sent as Unicode)
    • SQLBINARY or SQLVARYBIN (i dati vengono inviati senza conversione).SQLBINARY or SQLVARYBIN (the data is sent without any conversion).
  • Il database SQL di Azure e Azure SQL Data Warehouse supportano solo bcp.Azure SQL Database and Azure SQL Data Warehouse only support bcp. Per altre informazioni, vedere:For additional information, see:

Condizioni di test di esempioExample Test Conditions

Gli esempi di file di formato contenuti in questo argomento sono basati sulla tabella e sul file di dati definiti di seguito.The examples of format files in this topic are based on the table and data file defined below.

Tabella di esempioSample Table

Lo script seguente crea un database di prova e una tabella denominata myFirstImport.The script below creates a test database and a table named myFirstImport. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

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

File di dati di esempioSample Data File

Usando il Blocco note, creare un file D:\BCP\myFirstImport.bcp vuoto e inserire i dati seguenti:Using Notepad, create an empty file D:\BCP\myFirstImport.bcp and insert the following data:

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

In alternativa, è possibile eseguire lo script PowerShell seguente per creare e popolare il file di dati:Alternatively, you can execute the following PowerShell script to create and populate the data file:

cls
# 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;
Invoke-Item $bcpFile;

Creazione dei file di formatoCreating the Format Files

SQL Server supporta due tipi di file di formato, ovvero non XML e XML.SQL Server support two types of format file: non-XML format and XML format. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server.The non-XML format is the original format that is supported by earlier versions of SQL Server.

Creazione di un file di formato non XMLCreating a Non-XML Format File

Per informazioni dettagliate, vedere File in formato non XML (SQL Server) .Please review Non-XML Format Files (SQL Server) for detailed information. Il comando seguente userà l' utility bcp per generare un formato di file non XML, myFirstImport.fmt, sulla base dello schema di myFirstImport.The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare null anziché un percorso del file di dati.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L'opzione format richiede anche l'opzione -f .The format option also requires the -f option. Inoltre, in questo esempio il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campoe T viene usato per specificare una connessione trusted che usa la sicurezza integrata.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

Il file di formato non XML D:\BCP\myFirstImport.fmt dovrebbe essere simile a questo:Your non-XML format file, D:\BCP\myFirstImport.fmt should look as follows:

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              ""

Importante

Verificare che il file di formato non XML termini con un ritorno a capo/avanzamento riga.Ensure your non-XML format file ends with a carriage return\line feed. In caso contrario, è possibile che venga visualizzato il messaggio di errore seguente:Otherwise you will likely receive the following error message:

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

Creazione di un file di formato XMLCreating an XML Format File

Per informazioni dettagliate, vedere File in formato XML (SQL Server) .Please review XML Format Files (SQL Server) for detailed information. Il comando seguente userà l' utilità bcp per creare un file di formato XML, myFirstImport.xml, sulla base dello schema di myFirstImport.The following command will use the bcp utility to create an xml format file, myFirstImport.xml, based on the schema of myFirstImport. Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare null anziché un percorso del file di dati.To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. L'opzione format richiede sempre l'opzione -f e per creare un file di formato XML è necessario anche specificare l'opzione -x .The format option always requires the -f option, and to create an XML format file, you must also specify the -x option. Inoltre, in questo esempio il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campoe T viene usato per specificare una connessione trusted che usa la sicurezza integrata.In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

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

Il file di formato XML D:\BCP\myFirstImport.xml dovrebbe essere simile al seguente:Your XML format file, D:\BCP\myFirstImport.xml should look as follows:

\<?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="SQLVARYCHAR"/>
  \<COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

Utilizzo di un file di formato per l'importazione bulk dei datiUsing a Format File to Bulk Import Data

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.The examples below use the database, datafile, and format files created above.

Uso di bcp e di un file di formato non XMLUsing bcp and Non-XML Format File

Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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"

Uso di bcp e di un file di formato XMLUsing bcp and XML Format File

Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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;"

Uso di BULK INSERT e di un file di formato non XMLUsing BULK INSERT and Non-XML Format File

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Uso di BULK INSERT e di un file di formato XMLUsing BULK INSERT and XML Format File

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Uso di OPENROWSET(BULK...) e di un file di formato non XMLUsing OPENROWSET(BULK...) and Non-XML Format File

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Uso di OPENROWSET(BULK...) e di un file di formato XMLUsing OPENROWSET(BULK...) and XML Format File

Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management StudioSQL Server Management Studio (SSMS):

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;

Altri esempiMore examples!

Creazione di un file di formato (SQL Server)Create a Format File (SQL Server)
Utilizzo di un file di formato per ignorare una colonna di una tabella (SQL Server)Use a Format File to Skip a Table Column (SQL Server)
Utilizzo di un file di formato per escludere un campo di dati (SQL Server)Use a Format File to Skip a Data Field (SQL Server)
Utilizzo di un file di formato per eseguire il mapping tra le colonne della tabella e i campi del file di dati (SQL Server)Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

Vedere ancheSee also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
File in formato non XML (SQL Server) Non-XML Format Files (SQL Server)
File in formato XML (SQL Server)XML Format Files (SQL Server)
File di formato per l'importazione o l'esportazione di dati (SQL Server)Format Files for Importing or Exporting Data (SQL Server)