Utilizzo di un file di formato per ignorare una colonna di una tabella

In questo argomento viene utilizzato il file di formato non XML predefinito creato per la tabella di esempio myTestSkipCol utilizzando il comando bcp seguente:

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

Il comando precedente crea un file di formato non XML, myTestSkipCol_Default.fmt. Questo file di formato è detto file di formato predefinito in quanto è quello generato da bcp. In genere, un file di formato predefinito descrive una corrispondenza uno-a-uno tra i campi del file di dati e le colonne di tabella.

Nota sulla protezione
Potrebbe essere necessario specificare il nome dell'istanza del server al quale ci si connette. Potrebbe inoltre essere necessario specificare il nome utente e la password. Per ulteriori informazioni, vedere Utilità bcp.

Nella figura seguente vengono illustrati i valori nei file di formato predefinito di esempio. Nella figura viene inoltre indicato il nome di ogni campo del file di formato.

Per ignorare una colonna di tabella, modificare il file di formato non XML predefinito, utilizzando una delle modalità alternative seguenti:

• La modalità preferita include tre passaggi principali. Eliminare innanzitutto eventuali righe del file di formato che specifichino un campo non presente nel file di dati. Ridurre quindi il valore "Ordine dei campi nel file host" di ogni riga del file di formato che segue una riga eliminata. 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. Ridurre infine il valore nel campo "Numero di colonne" in modo da riflettere il numero effettivo di campi nel file di dati.

  L'esempio seguente si basa sul file di formato predefinito per la tabella myTestSkipCol, creato in "Creazione di un file di formato non XML predefinito", più indietro in questo argomento. Questo file di formato modificato esegue il mapping tra il primo campo dati e Col1, ignora Col2 ed esegue il mapping tra il secondo campo dati e Col3. La riga per Col2 è stata eliminata. Le altre modifiche sono indicate in grassetto:

  9.0
  2
  1       SQLCHAR       0       7       "\t"     1     Col1         ""
  2       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
  

• In alternativa, per ignorare una colonna di tabella, è possibile modificare la definizione della riga del file di formato che corrisponde alla colonna di tabella. 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. Inoltre, i campi "terminatore" e "regole di confronto a livello di colonna" devono essere impostati su "" (NULL).

  Il valore "nome della colonna del server" richiede una stringa non vuota, sebbene il nome di colonna effettivo non sia necessario. Per i campi di formato restanti sono necessari i relativi valori predefiniti.

  L'esempio seguente deriva inoltre dal file di formato predefinito per la tabella myTestSkipCol. I valori che devono essere 0 o NULL sono indicati in grassetto.

  9.0
  3
  1       SQLCHAR       0       7       "\t"     1     Col1         ""
  2       SQLCHAR       00""0     Col2         ""
  3       SQLCHAR       0       100     "\r\n"   3     Col3         SQL_Latin1_General_CP1_CI_AS
  

Gli esempi seguenti sono inoltre basati sulla tabella di esempio myTestSkipCol e sul file di dati di esempio myTestSkipCol2.dat creati in "File di dati e tabella di esempio", più indietro in questo argomento.

Utilizzo di BULK INSERT

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

Gli esempi dei file di formato modificati sono basati sulla tabella di esempio myTestSkipCol e sul file di dati di esempio creati in "File di dati e tabella di esempio", più indietro in questo argomento. Il comando bcp seguente crea un file di formato XML predefinito per la tabella myTestSkipCol:

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

Il file di formato non XML predefinito risultante descrive una corrispondenza uno-a-uno tra i campi del file di dati e le colonne di tabella, come segue:

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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>

Gli esempi in questa sezione utilizzano la tabella di esempio myTestSkipCol e il file di dati di esempio myTestSkipCol2.dat creati in "File di dati e tabella di esempio", più indietro in questo argomento. Per importare i dati da myTestSkipCol2.dat nella tabella myTestSkipCol, gli esempi utilizzano il file di formato XML modificato seguente, myTestSkipCol2-x.xml, basato sul file di formato creato in "Creazione di un file di formato XML predefinito" più indietro in questo argomento.

<?xml version="1.0"?>
<BCPFORMAT xmlns="https://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>

Utilizzo di OPENROWSET(BULK...)

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

Utilizzo di BULK IMPORT in una vista

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

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