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

  • Articolo

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. Questo articolo presenta file di formato non XML e XML modificati per contenere un file di dati i cui campi sono disposti in un ordine diverso rispetto alle 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 Creare un file di formato con bcp (SQL Server).

È possibile usare un file di formato non XML o XML per importare in blocco un file di dati nella tabella usando un comando di utilità bcp, un'istruzione BULK INSERT o INSERT ... ISTRUZIONE SELECT * FROM OPENROWSET. 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 di database cloud, eseguire lo spostamento dei dati tramite l'istruzione COPY in Azure Data Factory o usando istruzioni T-SQL, come COPY INTO e PolyBase.

Condizioni di test di esempio

Gli esempi di file di formato modificati in questo articolo si basano sulla tabella e sul file di dati definiti più avanti in questo articolo.

Tabella di esempio

Lo script Transact-SQL seguente crea un database di test e una tabella denominata myRemap.

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 seguenti presentano FirstName e LastName nell'ordine inverso, come presentato nella tabella myRemap. Usando un editor di testo come 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 importare in blocco i dati da myRemap.bcp nella myRemap tabella, il file di formato deve eseguire le azioni 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 Usare file di formato non XML (SQL Server). Il comando seguente usa l'utilità bcp per generare un file di formato non XML, myRemap.fmt, in base allo 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 informazioni sulla terminologia, vedere Struttura di file di formato non XML. Aprire D:\BCP\myRemap.fmt nel Blocco note e apportare le modifiche seguenti:

  1. Ridisporre 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. Assicurarsi che sia presente un ritorno a capo dopo l'ultima riga del file di formato.

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, vedi File in formato XML (SQL Server). Il comando seguente usa l'utilità bcp per creare un file di formato XML, myRemap.xml, in base allo 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 <FIELD> elementi vengono dichiarati nel file di formato è l'ordine in cui tali campi vengono visualizzati nel file di dati. Invertire quindi l'ordine degli <FIELD> elementi con attributi ID 2 e 3.

  2. Assicurarsi che i valori dell'attributo ID <FIELD> siano sequenziali.

  3. L'ordine <COLUMN> degli elementi nell'elemento <ROW> definisce l'ordine in cui l'operazione bulk li invia alla destinazione. Il file di formato XML assegna 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 <COLUMN> degli elementi è indipendente dall'ordine degli <FIELD> elementi 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, viene mappato alla terza colonna della tabella, myRemap.. LastName
  • FIELD 3, che corrisponde a COLUMN 3, viene 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 lo script Transact-SQL seguente.

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 lo script Transact-SQL seguente.

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;

Usare OPENROWSET e file di formato non XML

Eseguire lo script Transact-SQL seguente.

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;

Usare OPENROWSET e il file di formato XML

Eseguire lo script Transact-SQL seguente.

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;

Contenuto correlato