Usare un file di formato per eseguire il mapping tra le colonne della tabella e i campi del file di dati (SQL Server)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di sistemi analitici (PDW)

Un file di dati può includere campi disposti in un ordine diverso da quello delle colonne corrispondenti presenti nella tabella. In questo argomento vengono descritti file di formato sia non XML che XML, modificati per adattarli a un file di dati contenente campi disposti un ordine diverso da quello delle colonne della tabella. Il file di formato modificato esegue il mapping tra i campi dati e le colonne corrispondenti della tabella. 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 myRemap. Eseguire l'istruzione Transact-SQL seguente in Microsoft SQL Server Management Studio (SSMS):

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
CREATE TABLE myRemap
   (
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   Gender char(1)
   );

File di dati di esempio

I dati che seguono presentano FirstName e LastName in ordine inverso come illustrato nella tabella myRemap. Usando il Blocco note, creare un file D:\BCP\myRemap.bcp vuoto e inserire i dati seguenti:

1,Grosse,Anthony,M
2,Fatnowna,Alica,F
3,Rosenhain,Stella,F

Creazione dei file di formato

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

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

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 File in formato non XML (SQL Server) . Il comando seguente userà l' utility bcp per generare un formato di file non XML, myRemap.fmt, sulla base dello schema di myRemap. 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.myRemap format nul -c -f D:\BCP\myRemap.fmt -t, -T

Modifica del file di formato non XML

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

  1. Riorganizzare l'ordine delle righe del file di formato in modo che le righe siano nello stesso ordine dei dati in myRemap.bcp.
  2. Assicurarsi che i valori dell'ordine dei campi nel file host siano sequenziali.
  3. Verificare che dopo l'ultima riga del file di formato ci sia un ritorno a capo.

Confrontare le modifiche:

Prima

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

Dopo

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

Il file di formato modificato riflette ora:

  • Il primo campo dati in myRemap.bcp è mappato alla prima colonna, myRemap.. PersonID
  • Il secondo campo dati in myRemap.bcp è mappato alla terza colonna, myRemap.. LastName
  • Il terzo campo dati in myRemap.bcp è mappato alla seconda colonna, myRemap.. FirstName
  • Il quarto campo dati in myRemap.bcp è mappato alla quarta colonna, myRemap.. Gender

Creazione di un file di formato XML

Per informazioni dettagliate, vedere File in formato XML (SQL Server) . Il comando seguente userà l' utilità bcp per creare un file di formato XML, myRemap.xml, sulla base dello schema di myRemap. 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.myRemap format nul -c -x -f D:\BCP\myRemap.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\myRemap.xml nel Blocco note e apportare le modifiche seguenti:

  1. L'ordine in cui gli elementi <FIELD> vengono dichiarati nel file di formato corrisponde a quello in cui tali campi sono riportati nel file di dati, quindi invertire l'ordine degli elementi <FIELD> con attributi ID 2 e 3.
  2. Assicurarsi che i valori degli attributi ID <FIELD> siano sequenziali.
  3. L'ordine degli elementi <COLUMN> nell'elemento <ROW> definisce l'ordine in cui l'operazione in blocco invia tali elementi alla destinazione. Il file di formato XML assegna a ogni elemento <COLUMN> a un nome locale senza alcuna relazione con la colonna nella tabella di destinazione di un'operazione di importazione in blocco. L'ordine degli elementi <COLUMN> è indipendente da quello degli elementi <FIELD> in una definizione <RECORD>. Ogni elemento <COLUMN> corrisponde a un elemento <FIELD>, il cui ID viene specificato nell'attributo SOURCE dell'elemento <COLUMN>. Di conseguenza, i valori per <COLUMN> SOURCE sono gli unici attributi che richiedono revisione. Invertire l'ordine per gli attributi <COLUMN> SOURCE 2 e 3.

Confrontare le modifiche:

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="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="1" 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"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</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="30" 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="1" 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="2" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Gender" xsi:type="SQLCHAR"/>
</ROW>
</BCPFORMAT>

Il file di formato modificato riflette ora:

  • FIELD 1, che corrisponde a COLUMN 1, è mappato alla prima colonna della tabella, myRemap.. PersonID
  • FIELD 2, che corrisponde a COLUMN 2, è di nuovo mappato alla terza colonna della tabella, myRemap.. LastName
  • FIELD 3, che corrisponde a COLUMN 3, è di nuovo mappato alla seconda colonna della tabella, myRemap.. FirstName
  • FIELD 4, che corrisponde a COLUMN 4, è mappato alla quarta colonna della tabella, myRemap.. Gender

Importazione dei dati con un file di formato per eseguire il mapping tra le colonne della tabella e i campi del file 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.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.fmt -T

Uso di bcp e di un file di formato XML

Al prompt dei comandi immettere il comando seguente:

bcp TestDatabase.dbo.myRemap IN D:\BCP\myRemap.bcp -f D:\BCP\myRemap.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 myRemap;
BULK INSERT dbo.myRemap  
   FROM 'D:\BCP\myRemap.bcp'  
   WITH (FORMATFILE = 'D:\BCP\myRemap.fmt');  
GO

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

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

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

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

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

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

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

Passaggi successivi