Condividi tramite

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

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)

È possibile che un file di dati contenga un numero di campi maggiore del numero di colonne presenti nella tabella. Questo articolo descrive la modifica di file di formato non XML e XML per contenere un file di dati con più campi eseguendo il mapping delle colonne della tabella ai campi dati corrispondenti e ignorando i campi aggiuntivi.

Per altre informazioni, vedere Creare un file di formato con bcp (SQL Server).For more information, see Create a format file with bcp (SQL Server).

Nota

È possibile usare un file di formato non XML o XML per importare in blocco un file di dati nella tabella tramite un comando dell'utilità bcp, istruzione BULK INSERT (Transact-SQL) o istruzione INSERT ... SELECT * FROM OPENROWSET BULK (Transact-SQL). Per altre informazioni, vedere Usare un file di formato per l'importazione bulk di 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 in questo articolo si basano sulla tabella myTestSkipField di esempio e sul file di D:\BCP\myTestSkipField.bcpdati . Modificare il percorso del file locale nell'esempio di codice in un percorso di file nel computer.

Tabella di esempio

Lo script crea un database di test 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:

  • Mappa il primo campo dati alla prima colonna PersonID.
  • Esclusione del secondo campo dati.
  • Mappa il terzo campo dati alla seconda colonna FirstName.
  • Mappa il 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, vedere Usare file di 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:

  1. Copiare l'intera riga del file di formato per FirstName e incollarla direttamente dopo FirstName nella riga successiva.
  2. Aumentare di uno il valore di ordine dei campi del file host per la nuova riga e tutte le righe successive.
  3. Aumentare il valore del numero di colonne in modo da riflettere il numero effettivo di campi nel file di dati.
  4. 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, vedere File di 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.

  • Il qualificatore c viene usato per specificare i dati di caratteri
  • t, viene usato per specificare una virgola come carattere di terminazione del campo
  • T viene usato per specificare una connessione attendibile tramite 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:

  1. Copiare tutto il secondo campo e incollarlo immediatamente dopo il secondo campo nella riga successiva.
  2. Aumenta il valore di FIELD ID di 1 per il nuovo FIELD e per ogni FIELD successivo.
  3. Aumenta il valore di COLUMN SOURCE di 1 per FirstName e LastName per riflettere la mappatura aggiornata.

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 viene mappato alla prima colonna della tabella, myTestSkipField.. PersonID
  • FIELD 2 non corrisponde ad alcun COLUMN elemento e pertanto non è mappato ad alcuna colonna di tabella.
  • FIELD 3 che corrisponde a COLUMN 3 viene 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 saltare un campo di dati

Gli esempi usano il database di esempio, il file di dati e i file di formato creati in questo articolo.

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

Usare i file di formato bcp e XML (SQL Server)

Al prompt dei comandi immettere il comando seguente:

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

Usare BULK INSERT (Transact-SQL) e 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;

Usare BULK INSERT (Transact-SQL) e i file di formato XML (SQL Server)

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;

Usare OPENROWSET BULK (Transact-SQL) e 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;

Usare OPENROWSET BULK (Transact-SQL) e i file di formato XML (SQL Server)

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;

Contenuti correlati