Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)
Si applica a: SQL Server
Database SQL di Azure
Istanza gestita di SQL di Azure
Azure Synapse Analytics
Analytics Platform System (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:
- Riorganizzare l'ordine delle righe del file di formato in modo che le righe siano nello stesso ordine dei dati in
myRemap.bcp
. - Assicurarsi che i valori dell'ordine dei campi nel file host siano sequenziali.
- 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:
- 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.
- Assicurarsi che i valori degli attributi ID <FIELD> siano sequenziali.
- 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;