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)

In questo argomento si applica a: SìSQL ServerSìDatabase SQL di AzureSìAzure SQL Data Warehouse Sì Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Questo articolo descrive come usare un file di formato per evitare di importare la colonna di una tabella quando il dati per la colonna ignorata non esistono nel file di dati di origine.This article describes how to use a format file to skip importing a table column when the data for the skipped column does not exist in the source data file. Un file di dati può contenere un numero di campi inferiore rispetto al numero di colonne nella tabella di destinazione, ovvero è possibile ignorare l'importazione di una colonna, solo se viene soddisfatta almeno una delle due condizioni seguenti nella tabella di destinazione:A data file can contain fewer fields than the number of columns in the destination table - that is, you can skip importing a column - only if at least one of the following two conditions is true in the destination table:

  • La colonna ignorata ammette i valori Null.The skipped column is nullable.
  • La colonna ignorata ha un valore predefinito.The skipped column has a default value.

Tabella e file di dati di esempioSample table and data file

Gli esempi in questo articolo prevedono una tabella denominata myTestSkipCol nello schema dbo.The examples in this article expect a table named myTestSkipCol under the dbo schema. È possibile creare questa tabella in un database di esempio, ad esempio WideWorldImporters o AdventureWorks, o in qualsiasi altro database.You can create this table in a sample database such as WideWorldImporters or AdventureWorks or in any other database. Per creare la tabella, utilizzare il codice seguente:Create this table as follows:

USE WideWorldImporters;  
GO  
CREATE TABLE myTestSkipCol   
   (  
   Col1 smallint,  
   Col2 nvarchar(50) NULL,  
   Col3 nvarchar(50) not NULL  
   );  
GO  

Gli esempi in questo articolo usano anche un file di dati di esempio, myTestSkipCol2.dat.The examples in this article also use a sample data file, myTestSkipCol2.dat. Questo file di dati contiene solo due campi sebbene la tabella di destinazione contenga tre colonne.This data file contains only two fields, although the destination table contains three columns.

1,DataForColumn3  
1,DataForColumn3  
1,DataForColumn3  

Passaggi principaliBasic steps

È possibile usare un file di formato non XML o un file in formato XML per ignorare una colonna di tabella.You can use a non-XML format file or an XML format file to skip a table column. In entrambi i casi, sono previsti due passaggi:In both cases, there are two steps:

  1. Usare l'utilità della riga di comando bcp per creare un file di formato predefinito.Use the bcp command-line utility to create a default format file.

  2. Modificare il file di formato predefinito in un editor di testo.Modify the default format file in a text editor.

Il file di formato modificato deve eseguire il mapping di ogni campo esistente alla colonna corrispondente nella tabella di destinazione.The modified format file must map each existing field to its corresponding column in the destination table. IL file deve indicare anche la colonna o le colonne della tabella da ignorare.It must also indicate which table column or columns to skip.

Ad esempio, per eseguire l'importazione in blocco dei dati da myTestSkipCol2.dat nella tabella myTestSkipCol, è necessario che il file di formato esegua il mapping del primo campo di dati a Col1, che ignori Col2 e che esegua il mapping del secondo campo a Col3.For example, to bulk import data from myTestSkipCol2.dat into the myTestSkipCol table, the format file must map the first data field to Col1, skip Col2, and map the second field to Col3.

Opzione 1 - Usare un file di formato non XMLOption #1 - Use a non-XML format file

Passaggio 1 - Creare un file di formato non XML predefinitoStep #1 - Create a default non-XML format file

Creare un file di formato non XML predefinito per la tabella di esempio myTestSkipCol eseguendo il comando bcp seguente al prompt dei comandi:Create a default non-XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.fmt -c -T  

Importante

Potrebbe essere necessario specificare il nome dell'istanza del server a cui ci si connette usando l'argomento -S.You might have to specify the name of the server instance to which you are connecting with the -S argument. Potrebbe anche essere necessario specificare il nome utente e la password con gli argomenti -U e -P.Also, you might have to specify the user name and password with the -U and -P arguments. Per altre informazioni, vedere bcp Utility.For more information, see bcp Utility.

Il comando precedente crea un file di formato non XML, myTestSkipCol_Default.fmt.The previous command creates a non-XML format file, myTestSkipCol_Default.fmt. Questo file di formato è chiamato file di formato predefinito perché è generato da bcp.This format file is called a default format file because it is the form generated by bcp. Un file di formato predefinito descrive una corrispondenza uno-a-uno tra i campi del file di dati e le colonne di tabella.A default format file describes a one-to-one correspondence between data-file fields and table columns.

La figura seguente illustra i valori nei file di formato predefiniti di esempio.The following screenshot shows the values in this sample default format files.

file di formato non XML predefinito per myTestSkipColdefault non-XML format file for myTestSkipCol

Nota

Per altre informazioni sui campi dei file di formato, vedere File in formato non XML (SQL Server).For more information about the format-file fields, see Non-XML Format Files (SQL Server).

Passaggio 2 - Modificare un file di formato non XMLStep #2 - Modify a non-XML format file

Per modificare un file di formato non XML predefinito, esistono due alternative.To modify a default non-XML format file, there are two alternatives. Entrambe consentono di indicare che il campo dati non esiste nel file di dati e che non devono essere inseriti dati nella colonna della tabella corrispondente.Either alternative indicates that the data field does not exist in the data file and that no data is to be inserted into the corresponding table column.

Per ignorare una colonna di tabella, modificare il file di formato non XML predefinito, utilizzando una delle modalità alternative seguenti:To skip a table column, edit the default non-XML format file and modify the file by using one of the following alternative methods:

Opzione 1 - Rimuovere la rigaOption #1 - Remove the row

Il metodo preferito per ignorare una colonna prevede i tre passaggi seguenti:The preferred method for skipping a column involves the following three steps:

  1. Eliminare innanzitutto eventuali righe del file di formato che descrivono un campo non presente nel file di dati di origine.First, delete any format-file row that describes a field that is missing from the source data file.
  2. Ridurre quindi il valore "Ordine dei campi nel file host" di ogni riga del file di formato che segue una riga eliminata.Then, reduce the "Host file field order" value of each format-file row that follows a deleted row. L'obiettivo sono i valori sequenziali "Ordine dei campi nel file host", da 1 a n, che riflettono l'effettiva posizione di ogni campo nel file di dati.The goal is sequential "Host file field order" values, 1 through n, that reflect the actual position of each data field in the data file.
  3. Ridurre infine il valore nel campo "Numero di colonne" in modo da riflettere il numero effettivo di campi nel file di dati.Finally, reduce the value in the "Number of columns" field to reflect the actual number of fields in the data file.

L'esempio seguente si basa sul file di formato predefinito per la tabella myTestSkipCol.The following example is based on the default format file for the myTestSkipCol table. Questo file di formato modificato esegue il mapping tra il primo campo dati e Col1, ignora Col2ed esegue il mapping tra il secondo campo dati e Col3.This modified format file maps the first data field to Col1, skips Col2, and maps the second data field to Col3. La riga per Col2 è stata eliminata.The row for Col2 has been deleted. Anche il delimitatore dopo il primo campo è stato modificato da \t a ,.The delimiter after the first field has also been changed from \t to ,.

14.0  
2  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

Opzione 2 - Modificare la definizione di rigaOption #2 - Modify the row definition

In alternativa, per ignorare una colonna di tabella, è possibile modificare la definizione della riga del file di formato che corrisponde alla colonna di tabella.Alternatively, to skip a table column, you can modify the definition of the format-file row that corresponds to the table column. In questa riga del file di formato, i valori "lunghezza del prefisso," "lunghezza dei dati del file host" e "ordine delle colonne nel server" devono essere impostati su 0.In this format-file row, the "prefix length," "host file data length," and "server column order" values must be set to 0. Inoltre, i campi "terminatore" e "regole di confronto a livello di colonna" devono essere impostati su "" (ovvero un valore vuoto o NULL).Also, the "terminator" and "column collation" fields must be set to "" (that is, to an empty or NULL value). Il valore "nome colonna server" richiede una stringa non vuota, sebbene il nome di colonna effettivo non sia necessario.The "server column name" value requires a non-blank string, though the actual column name is not necessary. Per i campi di formato restanti sono necessari i relativi valori predefiniti.The remaining format fields require their default values.

L'esempio seguente deriva inoltre dal file di formato predefinito per la tabella myTestSkipCol .The following example is also derived from the default format file for the myTestSkipCol table.

14.0  
3  
1       SQLCHAR       0       7       ","      1     Col1         ""  
2       SQLCHAR       0       0       ""       0     Col2         ""  
3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS  

Esempi con un file di formato non XMLExamples with a non-XML format file

Gli esempi seguenti sono basati sulla tabella di esempio myTestSkipCol e sul file di dati di esempio myTestSkipCol2.dat precedentemente descritti in questo articolo.The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

Utilizzo di BULK INSERTUsing BULK INSERT

Questo esempio funziona usando uno dei file di formato non XML modificati creati come descritto nella sezione precedente.This example works by using either of the modified non-XML format files created as described in the preceding section. In questo esempio, il file di formato modificato è denominato myTestSkipCol2.fmt.In this example, the modified format file is named myTestSkipCol2.fmt. Per usare BULK INSERT per importare in blocco il file di dati myTestSkipCol2.dat, in SSMS, eseguire il codice seguente.To use BULK INSERT to bulk import the myTestSkipCol2.dat data file, in SSMS, run the following code. Aggiornare i percorsi di file system per il percorso dei file di esempio nel computer in uso.Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  
BULK INSERT myTestSkipCol   
   FROM 'C:\myTestSkipCol2.dat'   
   WITH (FORMATFILE = 'C:\myTestSkipCol2.fmt');  
GO  
SELECT * FROM myTestSkipCol;  
GO  

Opzione 2 - Usare un file di formato XMLOption #2 - Use an XML format file

Passaggio 1 - Creare un file di formato XML predefinitoStep #1 - Create a default XML format file

Creare un file di formato XML predefinito per la tabella di esempio myTestSkipCol eseguendo il comando bcp seguente al prompt dei comandi:Create a default XML format file for the myTestSkipCol sample table by running the following bcp command at the command prompt:

bcp WideWorldImporters..myTestSkipCol format nul -f myTestSkipCol_Default.xml -c -x -T  

Importante

Potrebbe essere necessario specificare il nome dell'istanza del server a cui ci si connette usando l'argomento -S.You might have to specify the name of the server instance to which you are connecting with the -S argument. Potrebbe anche essere necessario specificare il nome utente e la password con gli argomenti -U e -P.Also, you might have to specify the user name and password with the -U and -P arguments. Per altre informazioni, vedere bcp Utility.For more information, see bcp Utility.

Il comando precedente crea un file di formato XML, myTestSkipCol_Default.xml.The previous command creates an XML format file, myTestSkipCol_Default.xml. Questo file di formato è chiamato file di formato predefinito perché è generato da bcp.This format file is called a default format file because it is the form generated by bcp. Un file di formato predefinito descrive una corrispondenza uno-a-uno tra i campi del file di dati e le colonne di tabella.A default format file describes a one-to-one correspondence between data-file fields and table columns.

<?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="\t" MAX_LENGTH="7"/>  
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>  
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

Nota

Per informazioni sulla struttura dei file di formato XML, vedere File in formato XML (SQL Server).For information about the structure of XML format files, see XML Format Files (SQL Server).

Passaggio 2 - Modificare un file di formato XMLStep #2 - Modify an XML format file

Di seguito è riportato il file di formato XML modificato myTestSkipCol2.xml che ignora Col2.Here is the modified XML format file, myTestSkipCol2.xml, which skips Col2. Le voci FIELD e ROW per Col2 sono state rimosse e le altre voci sono state rinumerate.The FIELD and ROW entries for Col2 have been removed and the entries have been renumbered. Anche il delimitatore dopo il primo campo è stato modificato da \t a ,.The delimiter after the first field has also been changed from \t to ,.

<?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="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>  
 </RECORD>  
 <ROW>  
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>  
  <COLUMN SOURCE="2" NAME="Col3" xsi:type="SQLNVARCHAR"/>  
 </ROW>  
</BCPFORMAT>  

Esempi con un file di formato XMLExamples with an XML format file

Gli esempi seguenti sono basati sulla tabella di esempio myTestSkipCol e sul file di dati di esempio myTestSkipCol2.dat precedentemente descritti in questo articolo.The following examples are based on the myTestSkipCol sample table and the myTestSkipCol2.dat sample data file that are described earlier in this article.

Per importare i dati da myTestSkipCol2.dat nella tabella myTestSkipCol, gli esempi usano il file di formato XML modificato, myTestSkipCol2.xml.To import the data from myTestSkipCol2.dat into the myTestSkipCol table, the examples use the modified XML format file, myTestSkipCol2.xml.

Uso di BULK INSERT con una vistaUsing BULK INSERT with a view

Con un file di formato XML non è possibile ignorare una colonna quando si sta eseguendo l'importazione diretta in una tabella usando un comando bcp o un'istruzione BULK INSERT.With an XML format file, you cannot skip a column when you are importing directly into a table by using a bcp command or a BULK INSERT statement. È tuttavia possibile importare un'intera tabella a eccezione dell'ultima colonna.However, you can import into all but the last column of a table. Se si vuole ignorare qualsiasi colonna diversa dall'ultima, è necessario creare una vista della tabella di destinazione che contiene solo le colonne contenute nel file di dati.If you have to skip any column other than the last column, you must create a view of the target table that contains only the columns contained in the data file. In seguito sarà possibile eseguire un'importazione bulk dei dati da tale file nella vista.Then, you can bulk import data from that file into the view.

L'esempio seguente crea la visualizzazione v_myTestSkipCol nella tabella myTestSkipCol.The following example creates the v_myTestSkipCol view on the myTestSkipCol table. Questa vista ignora la seconda colonna della tabella, Col2.This view skips the second table column, Col2. Nell'esempio viene quindi utilizzato BULK INSERT per importare il file di dati myTestSkipCol2.dat in questa vista.The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

In SSMS eseguire il codice seguente.In SSMS, run the following code. Aggiornare i percorsi di file system per il percorso dei file di esempio nel computer in uso.Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  

CREATE VIEW v_myTestSkipCol AS  
    SELECT Col1,Col3  
    FROM myTestSkipCol;  
GO  

BULK INSERT v_myTestSkipCol  
FROM 'C:\myTestSkipCol2.dat'  
WITH (FORMATFILE='C:\myTestSkipCol2.xml');  
GO  

Utilizzo di OPENROWSET(BULK...)Using OPENROWSET(BULK...)

Per usare un file di formato XML per ignorare una colonna di tabella usando OPENROWSET(BULK...), è necessario specificare un elenco esplicito di colonne nell'elenco di selezione e anche nella tabella di destinazione, come illustrato di seguito:To use an XML format file to skip a table column by using OPENROWSET(BULK...), you have to provide an explicit list of columns in the select list and also in the target table, as follows:

```sql
INSERT ...<column_list> SELECT <column_list> FROM OPENROWSET(BULK...) 
```

Nell'esempio seguente viene utilizzato il provider di set di righe con lettura bulk OPENROWSET e il file di formato myTestSkipCol2.xml .The following example uses the OPENROWSET bulk rowset provider and the myTestSkipCol2.xml format file. Nell'esempio viene eseguita l'importazione bulk del file di dati myTestSkipCol2.dat nella tabella myTestSkipCol .The example bulk imports the myTestSkipCol2.dat data file into the myTestSkipCol table. L'istruzione contiene un elenco esplicito di colonne nell'elenco selezionato e nella tabella di destinazione, come richiesto.The statement contains an explicit list of columns in the select list and also in the target table, as required.

In SSMS eseguire il codice seguente.In SSMS, run the following code. Aggiornare i percorsi di file system per il percorso dei file di esempio nel computer in uso.Update the file system paths for the location of the sample files on your computer.

USE WideWorldImporters;  
GO  
INSERT INTO myTestSkipCol  
  (Col1,Col3)  
    SELECT Col1,Col3  
      FROM  OPENROWSET(BULK  'C:\myTestSkipCol2.Dat',  
      FORMATFILE='C:\myTestSkipCol2.Xml'    
       ) as t1 ;  
GO  

Vedere ancheSee Also

bcp Utility bcp Utility
BULK INSERT (Transact-SQL) BULK INSERT (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
Utilizzo di un file di formato per ignorare una colonna di una tabella (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)
Utilizzo di un file di formato per l'importazione bulk dei dati (SQL Server)Use a Format File to Bulk Import Data (SQL Server)