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)

Un file di dati può includere campi disposti in un ordine diverso da quello delle colonne corrispondenti presenti nella tabella.A data file can contain fields arranged in a different order from the corresponding columns in the table. In questo argomento vengono descritti file di formato sia non XML che XML, modificati per adattarli a un file di dati contenente campi disposti un ordine diverso da quello delle colonne della tabella.This topic presents both non-XML and XML format files that have been modified to accommodate a data file whose fields are arranged in a different order from the table columns. Il file di formato modificato esegue il mapping tra i campi dati e le colonne corrispondenti della tabella.The modified format file maps the data fields to their corresponding table columns. Per altre informazioni, vedere Creazione di un file di formato (SQL Server) .Please review Create a Format File (SQL Server) for additional information.

RiquadroOutline
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
 ● Modifica del file di formato non XML ● Modifying the Non-XML Format File
 ● Creazione di un file di formato XML ● Creating an XML Format File
 ● Modifica del file di formato XML ● Modifying the XML Format File
Importazione dei dati con un file di formato per eseguire il mapping tra le colonne della tabella e i campi del file di datiImporting Data with a Format File to Map Table Columns to Data-File Field
 ● 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

Nota

È possibile usare un file di formato non XML o XML per importare in blocco un file di dati nella tabella usando un comando dell'utilità bcp, l'istruzione BULK INSERT o l'istruzione INSERT... Istruzione SELECT * FROM OPENROWSET(BULK...).Either a non-XML or XML format file can be used to bulk import a data file into the table by using a bcp utility command, BULK INSERT statement, or INSERT ... SELECT * FROM OPENROWSET(BULK...) statement. Per altre informazioni, vedere Usare un file di formato per l'importazione in blocco dei dati (SQL Server).For more information, see Use a Format File to Bulk Import Data (SQL Server).

Condizioni di test di esempioExample Test Conditions

Gli esempi di file di formato modificati contenuti in questo argomento sono basati sulla tabella e sul file di dati definiti di seguito.The examples of modified 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 myRemap.The script below creates a test database and a table named myRemap. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

File di dati di esempioSample Data File

I dati che seguono presentano FirstName e LastName in ordine inverso come illustrato nella tabella myRemap.The data below presents FirstName and LastName in the reverse order as presented in the table myRemap. Usando il Blocco note, creare un file D:\BCP\myRemap.bcp vuoto e inserire i dati seguenti:Using Notepad, create an empty file D:\BCP\myRemap.bcp and insert the following data:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Creazione dei file di formatoCreating the Format Files

Per eseguire un'importazione bulk dei dati da myRemap.bcp nella tabella myRemap , è necessario che il file di formato esegua le operazioni seguenti:To bulk import data from myRemap.bcp into the myRemap table, the format file must do the following:

  • Mapping del primo campo dati alla prima colonna PersonID.Map the first data field to the first column, PersonID.
  • Mapping del secondo campo dati alla terza colonna LastName.Map the second data field to the third column, LastName.
  • Mapping del terzo campo dati alla seconda colonna FirstName.Map the third data field to the second column, FirstName.
  • Mapping del quarto campo dati alla quarta colonna Gender.Map the fourth data field to the fourth column, Gender.

Il metodo più semplice per creare il file di formato consiste nell'usare l' utilità bcp.The simplest method to create the format file is by using the bcp utility. Prima di tutto, creare un file di formato di base dalla tabella esistente.First, create a base format file from the existing table. In secondo luogo, modificare il file di formato di base in modo da riflettere il file di dati effettivo.Second, modify the base format file to reflect the actual data file.

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 file di formato non XML, myRemap.fmt, sulla base dello schema di myRemap.The following command will use the bcp utility to generate a non-xml format file, myRemap.fmt, based on the schema of myRemap. Inoltre, il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campo e T viene usato per specificare una connessione trusted che usa la sicurezza integrata.In addition, 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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Modifica del file di formato non XML Modifying the Non-XML Format File

Per la terminologia, vedere Struttura dei file di formato non XML .See Structure of Non-XML Format Files for terminology. Aprire D:\BCP\myRemap.fmt nel Blocco note e apportare le modifiche seguenti:Open D:\BCP\myRemap.fmt in Notepad and perform the following modifications: 1) Riorganizzare l'ordine delle righe del file di formato in modo che le righe siano nello stesso ordine dei dati in myRemap.bcp.Re-arrange the order of the format-file rows so that the rows are in the same order as the data in myRemap.bcp. 2) Assicurarsi che i valori dell'ordine dei campi nel file host siano sequenziali.Ensure the host file field order values are sequential. 3) Verificare che dopo l'ultima riga del file di formato ci sia un ritorno a capo.Ensure there is a carriage return after the last format-file row.

Confrontare le modifiche:Compare the changes:
PrimaBefore

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       1       "\r\n"   4     Gender                 SQL_Latin1_General_CP1_CI_AS

AfterAfter

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

Il file di formato modificato riflette ora:The modified format file now reflects:

  • Il primo campo dati in myRemap.bcp è mappato alla prima colonna, myRemap.. PersonIDThe first data field in myRemap.bcp is mapped to the first column, myRemap.. PersonID
  • Il secondo campo dati in myRemap.bcp è mappato alla terza colonna, myRemap.. LastNameThe second data field in myRemap.bcp is mapped to the third column, myRemap.. LastName
  • Il terzo campo dati in myRemap.bcp è mappato alla seconda colonna, myRemap.. FirstNameThe third data field in myRemap.bcp is mapped to the second column, myRemap.. FirstName
  • Il quarto campo dati in myRemap.bcp è mappato alla quarta colonna, myRemap.. GenderThe fourth data field in myRemap.bcp is mapped to the fourth column, myRemap.. Gender

Creazione di un file di formato XML Creating 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, myRemap.xml, sulla base dello schema di myRemap.The following command will use the bcp utility to create an xml format file, myRemap.xml, based on the schema of myRemap. Inoltre, il qualificatore c viene usato per specificare dati di tipo carattere, t, viene usato per specificare la virgola come carattere di terminazione del campo e T viene usato per specificare una connessione trusted che usa la sicurezza integrata.In addition, 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. È necessario usare il qualificatore x per generare un file di formato basato su XML.The x qualifier must be used to generate an XML-based format file. Al prompt dei comandi immettere il comando seguente:At a command prompt, enter the following command:

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

Modifica del file di formato XML Modifying the XML Format File

Per la terminologia, vedere Sintassi dello schema per i file di formato XML .See Schema Syntax for XML Format Files for terminology. Aprire D:\BCP\myRemap.xml nel Blocco note e apportare le modifiche seguenti:Open D:\BCP\myRemap.xml in Notepad and perform the following modifications: 1) L'ordine con cui gli elementi <FIELD> vengono dichiarati nel file di formato corrisponde a quello con cui tali campi sono riportati nel file di dati, quindi invertire l'ordine degli elementi <FIELD> con attributi ID 2 e 3.The order in which the <FIELD> elements are declared in the format file is the order in which those fields appear in the data file, thus reverse the order for the <FIELD> elements with ID attributes 2 and 3. 2) Assicurarsi che i valori dell'attributo ID <FIELD> siano sequenziali.Ensure the <FIELD> ID attribute values are sequential. 3) L'ordine degli elementi <COLUMN> nell'elemento <ROW> definisce l'ordine in cui tali elementi vengono restituiti dall'operazione in blocco.The order of the <COLUMN> elements in the <ROW> element defines the order in which they are returned by the bulk operation. Il file di formato XML assegna ogni elemento <COLUMN> a un nome locale senza alcuna relazione con la colonna nella tabella di destinazione di un'operazione di importazione in blocco.The XML format file assigns each <COLUMN> element a local name that has no relationship to the column in the target table of a bulk import operation. L'ordine degli elementi <COLUMN> è indipendente da quello degli elementi <FIELD> in una definizione <RECORD>.The order of the <COLUMN> elements is independent of the order of <FIELD> elements in a <RECORD> definition. Ogni elemento <COLUMN> corrisponde a un elemento <FIELD>, il cui ID viene specificato nell'attributo SOURCE dell'elemento <COLUMN>.Each <COLUMN> element corresponds to a <FIELD> element (whose ID is specified in the SOURCE attribute of the <COLUMN> element). Di conseguenza, i valori per <COLUMN> SOURCE sono gli unici attributi che richiedono revisione.Thus, the values for <COLUMN> SOURCE are the only attributes that require revision. Invertire l'ordine per gli attributi <COLUMN> SOURCE 2 e 3.Reverse the order for <COLUMN> SOURCE attributes 2 and 3.

Confrontare le modificheCompare the changes
PrimaBefore

\<?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="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </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="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

AfterAfter

\<?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="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  \<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  \<COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  \<COLUMN SOURCE="3" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  \<COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
 </ROW>
</BCPFORMAT>

Il file di formato modificato riflette ora:The modified format file now reflects:

  • FIELD 1, che corrisponde a COLUMN 1, è mappato alla prima colonna della tabella, myRemap.. PersonIDFIELD 1, which corresponds to COLUMN 1, is mapped to the first table column, myRemap.. PersonID
  • FIELD 2, che corrisponde a COLUMN 2, è di nuovo mappato alla terza colonna della tabella, myRemap.. LastNameFIELD 2, which corresponds to COLUMN 2, is re-mapped to the third table column, myRemap.. LastName
  • FIELD 3, che corrisponde a COLUMN 3, è di nuovo mappato alla seconda colonna della tabella, myRemap.. FirstNameFIELD 3, which corresponds to COLUMN 3, is re-mapped to the second table column, myRemap.. FirstName
  • FIELD 4, che corrisponde a COLUMN 4, è mappato alla quarta colonna della tabella, myRemap.. GenderFIELD 4, which corresponds to COLUMN 4, is mapped to the fourth table column, myRemap.. Gender

Importazione dei dati con un file di formato per eseguire il mapping tra le colonne della tabella e i campi del file di datiImporting Data with a Format File to Map Table Columns to Data-File Field

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:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

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:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.xml -T

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 Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

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 Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

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 Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;
GO

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

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

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 Studio (SSMS):Execute the following Transact-SQL in Microsoft SQL Server Management Studio (SSMS):

USE TestDatabase;  
GO

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

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

Vedere ancheSee Also

bcp Utility bcp Utility
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)