Formattare i file per importare ed esportare i dati (SQL Server)

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzurePiattaforma di strumenti analitici (PDW)

Quando si importando dati in blocco in una tabella di SQL Server o si esportano dati in blocco da una tabella, è possibile usare un file di formato per archiviare le informazioni sul formato necessarie all'esportazione o all'importazione in blocco dei dati. Sono incluse informazioni sul formato per ogni campo in un file di dati relativo a quella tabella.

SQL Server supporta due tipi di file di formato: file di formato XML e file di formato non XML. In entrambi i tipi di file di formato XML e non XML sono contenute descrizioni di ogni campo in un file di dati; nei file di formato XML sono inoltre presenti descrizioni delle colonne della tabella corrispondente. In generale, i file di formato XML e non XML sono intercambiabili. È tuttavia consigliabile utilizzare la sintassi XML per i nuovi file di formato, in quanto questo tipo di file offre numerosi vantaggi rispetto ai file di formato non XML. Per altre informazioni, vedere File in formato XML (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.

Vantaggi dei file di formato

I file di formato forniscono un sistema flessibile per la scrittura di file di dati che non richiede alcuna modifica o richiede modifiche minime per la conformità con altri formati di dati o la lettura di file di dati da un altro prodotto software.

É possibile eseguire l'importazione in blocco dei dati senza necessità di aggiungere o eliminare dati superflui o di ripetere l'ordinamento dei dati esistenti nel file di dati. I file di formato possono essere utili quando è presente una mancata corrispondenza tra i campi nel file di dati e le colonne nella tabella.

Esempi di file di formato

Negli esempi seguenti viene illustrato il layout di un file di formato non XML e di un file di formato XML. Questi file di formato corrispondono alla tabella HumanResources.myTeam nel database di esempio AdventureWorks2022 . Questa tabella contiene quattro colonne: EmployeeID, Name, Titlee ModifiedDate.

Nota

Per informazioni su questa tabella e su come crearla, vedere Tabella di esempio HumanResources.myTeam (SQL Server).

R. Uso di un file di formato non XML

Il file di formato non XML seguente utilizza il formato di dati nativo di SQL Server per la tabella HumanResources.myTeam. Questo file di formato è stato creato utilizzando il comando bcp seguente.

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T

Per impostazione predefinita, il comando bcp è un'istanza predefinita locale di SQL Server con l'autenticazione di Windows. É possibile specificare altre istanze e informazioni di accesso desiderate. Per altre informazioni, vedere Utilità bcp. Ad esempio, per specificare un'istanza remota denominata del server con l'autenticazione di Windows, usare:

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.Fmt -n -T -S servername/instancename

Il contenuto di questo file di formato è il seguente, iniziando dal numero di versione principale di SQL Server e dalle informazioni sui metadati della tabella.

14.0
4
1       SQLSMALLINT   0       2       ""   1     EmployeeID               ""
2       SQLNCHAR      2       100     ""   2     Name                     SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     Title                    SQL_Latin1_General_CP1_CI_AS
4       SQLNCHAR      2       100     ""   4     Background               SQL_Latin1_General_CP1_CI_AS

Per altre informazioni, vedere Usare file in formato non XML (SQL Server).

B. Usare un file di formato XML

Il seguente file di formato XML utilizza il formato nativo di dati di SQL Server per la tabella HumanResources.myTeam. Questo file di formato è stato creato utilizzando il comando bcp seguente.

bcp AdventureWorks2022.HumanResources.myTeam format nul -f myTeam.xml -x -n -T

Il file di formato contiene:

<?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="NativePrefix" LENGTH="1"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="EmployeeID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Title" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="Background" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

Per altre informazioni, vedere File in formato XML (SQL Server).

Quando è necessario un file di formato?

In genere, i file di formato sono necessari nelle seguenti circostanze:

  • Quando si usa un'istruzione INSERT ... SELECT * FROM OPENROWSET(BULK...).

  • Per situazioni complesse di importazione in blocco tramite bcp o BULK INSERT.

  • Viene utilizzato lo stesso file di dati come origine per più tabelle con schemi diversi.

  • I campi nel file di dati sono in numero diverso rispetto alle colonne della tabella di destinazione, ad esempio:

    • La tabella di destinazione include almeno una colonna per la quale un valore predefinito è definito o è consentito NULL.
    • L'utente non dispone di autorizzazioni SELECT/INSERT su una o più colonne della tabella.
    • Un singolo file di dati è utilizzato con due o più tabelle con schemi diversi.

  • L'ordine delle colonne è diverso nel file di dati rispetto alla tabella.

  • I caratteri di terminazione o le lunghezze di prefisso sono diverse all'interno delle colonne del file di dati.

Nota

In assenza di un file di formato, se in un comando bcp viene specificata un'opzione di formato di dati (-n, -c, -w o -N) o se in un'operazione BULK INSERT viene specificata l'opzione DATAFILETYPE, il formato di dati specificato viene usato come metodo predefinito per l'interpretazione dei campi del file di dati.

Contenuto correlato