Usare un file di formato per escludere un campo dati (SQL Server)

Articolo
03/13/2024

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW)

È possibile che un file di dati contenga un numero di campi maggiore del numero di colonne presenti nella tabella. 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. Per altre informazioni, vedere Creazione di un file di formato (SQL Server) .

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...) . Per altre informazioni, vedere Usare un file di formato per l'importazione in blocco dei dati (SQL Server).

Nota

Questa sintassi, incluso l’inserimento in blocco, non è supportata in Azure Synapse Analytics. In Azure Synapse Analytics e in altre integrazioni della piattaforma database cloud, lo spostamento dei dati si esegue tramite l'istruzione COPY in Azure Data Factory o usando istruzioni T-SQL, ad esempio COPY INTO e PolyBase.

Condizioni di test di esempio

Gli esempi di file di formato modificati contenuti in questo argomento sono basati sulla tabella e sul file di dati definiti di seguito.

Tabella di esempio

Lo script seguente crea un database di prova e una tabella denominata myTestSkipField. Eseguire l'istruzione Transact-SQL seguente 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 esempio

Creare un file vuoto D:\BCP\myTestSkipField.bcp e inserire i dati seguenti:

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

Creazione dei file di formato

Per eseguire un'importazione bulk dei dati da myTestSkipField.bcp nella tabella myTestSkipField , è necessario che il file di formato esegua le operazioni seguenti:

Mapping del primo campo dati alla prima colonna PersonID.
Esclusione del secondo campo dati.
Mapping del terzo campo dati alla seconda colonna FirstName.
Mapping del quarto campo dati alla terza colonna LastName.

Il metodo più semplice per creare il file di formato consiste nell'usare l' utilità bcp. Prima di tutto, creare un file di formato di base dalla tabella esistente. In secondo luogo, modificare il file di formato di base in modo da riflettere il file di dati effettivo.

Creazione di un file di formato non XML

Per informazioni dettagliate, vedi File in formato non XML (SQL Server). Il comando seguente userà l' utility bcp per generare un formato di file non XML, myTestSkipField.fmt, sulla base dello schema di 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. Al prompt dei comandi immettere il comando seguente:

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

Modifica del file di formato non XML

Per la terminologia, vedi Struttura dei file di formato non XML. Aprire D:\BCP\myTestSkipField.fmt nel Blocco note e apportare le modifiche seguenti:

Copiare l'intera riga del file di formato per FirstName e incollarla direttamente dopo FirstName nella riga successiva.
Aumentare di uno il valore di ordine dei campi del file host per la nuova riga e tutte le righe successive.
Aumentare il valore del numero di colonne in modo da riflettere il numero effettivo di campi nel file di dati.
Modificare l'ordine delle colonne del server da 2 a 0 per la seconda riga di file di formato.

Confrontare le modifiche apportate:

Prima

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

Dopo

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:

4 campi dati
Il primo campo dati in myTestSkipField.bcp è mappato alla prima colonna, myTestSkipField.. PersonID
Il secondo campo dati myTestSkipField.bcp non è mappato ad alcuna colonna.
Il terzo campo dati in myTestSkipField.bcp è mappato alla seconda colonna, myTestSkipField.. FirstName
Il quarto campo dati in myTestSkipField.bcp è mappato alla terza colonna, myTestSkipField.. LastName

Creazione di un file di formato XML

Per informazioni dettagliate, vedi File in formato XML (SQL Server). Il comando seguente userà l' utilità bcp per creare un file di formato XML, myTestSkipField.xml, sulla base dello schema di 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. È necessario usare il qualificatore x per generare un file di formato basato su XML. Al prompt dei comandi immettere il comando seguente:

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

Modifica del file di formato XML

Per la terminologia, vedi Sintassi dello schema per i file di formato XML. Aprire D:\BCP\myTestSkipField.xml nel Blocco note e apportare le modifiche seguenti:

Copiare tutto il secondo campo e incollarlo immediatamente dopo il secondo campo nella riga successiva.
Aumentare di 1 il valore "FIELD ID"per il nuovo campo e per ogni campo successivo.
Aumentare di 1 il valore "COLUMN SOURCE" per FirstNamee LastName in modo da riflettere il mapping modificato.

Confrontare le modifiche apportate:

Prima

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

Dopo

<?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:

4 campi dati
FIELD 1, che corrisponde a COLUMN 1, è mappato alla prima colonna della tabella, myTestSkipField.. PersonID
FIELD 2 non corrisponde ad alcun valore COLUMN, per cui non è mappato ad alcuna colonna della tabella.
FIELD 3, che corrisponde a COLUMN 3, è mappato alla seconda colonna della tabella, myTestSkipField.. FirstName
FIELD 4, che corrisponde a COLUMN 4, è mappato alla terza colonna della tabella, myTestSkipField.. LastName

Importazione di dati con un file di formato per escludere un campo di dati

Gli esempi seguenti usano il database, il file di dati e i file di formato creati in precedenza.

Uso di bcp e di un file di formato non XML

Al prompt dei comandi immettere il comando seguente:

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

Uso di bcp e di un file di formato XML

Al prompt dei comandi immettere il comando seguente:

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 XML

Eseguire l'istruzione Transact-SQL seguente 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 XML

Eseguire l'istruzione Transact-SQL seguente 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 XML

Eseguire l'istruzione Transact-SQL seguente 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 XML

Eseguire l'istruzione Transact-SQL seguente 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;

Condividi tramite

Usare un file di formato per escludere un campo dati (SQL Server)

Condizioni di test di esempio

Tabella di esempio

File di dati di esempio

Creazione dei file di formato

Creazione di un file di formato non XML

Modifica del file di formato non XML

Creazione di un file di formato XML

Modifica del file di formato XML

Importazione di dati con un file di formato per escludere un campo di dati

Uso di bcp e di un file di formato non XML

Uso di bcp e di un file di formato XML

Uso di BULK INSERT e di un file di formato non XML

Uso di BULK INSERT e di un file di formato XML

Uso di OPENROWSET(BULK...) e di un file di formato non XML

Uso di OPENROWSET(BULK...) e di un file di formato XML

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive