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)

È possibile che un file di dati contenga un numero di campi maggiore del numero di colonne presenti nella tabella.A data file can contain more fields than the number of columns in the table. In questo argomento viene descritta la modifica dei file di formato XML e non XML per consentire l'utilizzo di un file di dati con un numero maggiore di campi tramite il mapping delle colonne della tabella ai campi dati corrispondenti e l'esclusione dei campi aggiuntivi.This topic describes modifying both non-XML and XML format files to accommodate a data file with more fields by mapping the table columns to the corresponding data fields and ignoring the extra fields. 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 di un file di formato non XML ● Modifying a Non-XML Format File
 ● Creazione di un file di formato XML ● Creating an XML Format File
 ● Modifica di un file di formato XML ● Modifying an XML Format File
Importazione di dati con un file di formato per escludere un campo di datiImporting Data with a Format File to skip a Data 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 myTestSkipField.The script below creates a test database and a table named myTestSkipField. 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 myTestSkipField
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30)
   );

File di dati di esempioSample Data File

Creare un file vuoto D:\BCP\myTestSkipField.bcp e inserire i dati seguenti:Create an empty file D:\BCP\myTestSkipField.bcp and insert the following data:

1,SkipMe,Anthony,Grosse
2,SkipMe,Alica,Fatnowna
3,SkipMe,Stella,Rosenhain

Creazione dei file di formatoCreating the Format Files

Per eseguire un'importazione bulk dei dati da myTestSkipField.bcp nella tabella myTestSkipField , è necessario che il file di formato esegua le operazioni seguenti:To bulk import data from myTestSkipField.bcp into the myTestSkipField 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.
  • Esclusione del secondo campo dati.Skip the second data field.
  • 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 terza colonna LastName.Map the fourth data field to the third column, LastName.

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, myTestSkipField.fmt, sulla base dello schema di myTestSkipField.The following command will use the bcp utility to generate a non-xml format file, myTestSkipField.fmt, based on the schema of myTestSkipField. 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.myTestSkipField format nul -c -f D:\BCP\myTestSkipField.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\myTestSkipField.fmt nel Blocco note e apportare le modifiche seguenti:Open D:\BCP\myTestSkipField.fmt in Notepad and perform the following modifications: 1) Copiare l'intera riga del file di formato per FirstName e incollarla direttamente dopo FirstName nella riga successiva.Copy the entire format-file row for FirstName and paste it directly after FirstName on the next line. 2) Aumentare di uno il valore di ordine dei campi del file host per la nuova riga e tutte le righe successive.Increase the host file field order value by one for the new row and all subsequent rows. 3) Aumentare il valore del numero di colonne in modo da riflettere il numero effettivo di campi nel file di dati.Increase the number of columns value to reflect the actual number of fields in the data file. 3) Modificare l'ordine delle colonne del server da 2 a 0 per la seconda riga di file di formato.Modify the server column order from 2 to 0 for the second format-file row.

Confrontare le modifiche apportate:Compare the changes made:
PrimaBefore

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

AfterAfter

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

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

  • 4 campi dati4 data fields
  • Il primo campo dati in myTestSkipField.bcp è mappato alla prima colonna, myTestSkipField.. PersonIDThe first data field in myTestSkipField.bcp is mapped to the first column, myTestSkipField.. PersonID
  • Il secondo campo dati myTestSkipField.bcp non è mappato ad alcuna colonna.The second data field in myTestSkipField.bcp is not mapped to any column.
  • Il terzo campo dati in myTestSkipField.bcp è mappato alla seconda colonna, myTestSkipField.. FirstNameThe third data field in myTestSkipField.bcp is mapped to the second column, myTestSkipField.. FirstName
  • Il quarto campo dati in myTestSkipField.bcp è mappato alla terza colonna, myTestSkipField.. LastNameThe fourth data field in myTestSkipField.bcp is mapped to the third column, myTestSkipField.. LastName

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, myTestSkipField.xml, sulla base dello schema di myTestSkipField.The following command will use the bcp utility to create an xml format file, myTestSkipField.xml, based on the schema of myTestSkipField. 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.myTestSkipField format nul -c -x -f D:\BCP\myTestSkipField.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\myTestSkipField.xml nel Blocco note e apportare le modifiche seguenti:Open D:\BCP\myTestSkipField.xml in Notepad and perform the following modifications: 1) Copiare tutto il secondo campo e incollarlo immediatamente dopo il secondo campo nella riga successiva.Copy the entire second field and paste it directly after the second field on the next line. 2) Aumentare di 1 il valore "FIELD ID"per il nuovo campo e per ogni campo successivo.Increase the "FIELD ID" value by 1 for the new FIELD and for each subsequent FIELD. 3) Aumentare di 1 il valore "COLUMN SOURCE" per FirstNamee LastName in modo da riflettere il mapping modificato.Increase the "COLUMN SOURCE" value by 1 for FirstName, and LastName to reflect the revised mapping.

Confrontare le modifiche apportate:Compare the changes made:
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="\r\n" MAX_LENGTH="30" 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"/>
 </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="25" 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="30" 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="4" NAME="LastName" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

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

  • 4 campi dati4 data fields
  • FIELD 1, che corrisponde a COLUMN 1, è mappato alla prima colonna della tabella, myTestSkipField.. PersonIDFIELD 1 which corresponds to COLUMN 1 is mapped to the first table column, myTestSkipField.. PersonID
  • FIELD 2 non corrisponde ad alcun valore COLUMN, per cui non è mappato ad alcuna colonna della tabella.FIELD 2 does not correspond to any COLUMN and thus, is not mapped to any table column.
  • FIELD 3, che corrisponde a COLUMN 3, è mappato alla seconda colonna della tabella, myTestSkipField.. FirstNameFIELD 3 which corresponds to COLUMN 3 is mapped to the second table column, myTestSkipField.. FirstName
  • FIELD 4, che corrisponde a COLUMN 4, è mappato alla terza colonna della tabella, myTestSkipField.. LastNameFIELD 4 which corresponds to COLUMN 4 is mapped to the third table column, myTestSkipField.. LastName

Importazione di dati con un file di formato per escludere un campo di datiImporting Data with a Format File to skip a Data 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.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.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.myTestSkipField IN D:\BCP\myTestSkipField.bcp -f D:\BCP\myTestSkipField.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 myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.fmt');  
GO  

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

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 myTestSkipField;
BULK INSERT dbo.myTestSkipField   
   FROM 'D:\BCP\myTestSkipField.bcp'   
   WITH (FORMATFILE = 'D:\BCP\myTestSkipField.xml');  
GO  

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

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 myTestSkipField;
INSERT INTO dbo.myTestSkipField
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.fmt'
        ) AS t1;
GO

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

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 myTestSkipField;
INSERT INTO dbo.myTestSkipField 
    SELECT *
    FROM OPENROWSET (
        BULK 'D:\BCP\myTestSkipField.bcp',
        FORMATFILE = 'D:\BCP\myTestSkipField.xml'  
       ) AS t1;
GO

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

Vedere ancheSee Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Usare un file di formato per ignorare una colonna di una tabella (SQL Server) Use a Format File to Skip a Table Column (SQL Server)
Usare 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)