Creare un file di formato con bcp (SQL Server)

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

Questo articolo illustra come usare l'utilità bcp per creare un file di formato per una tabella specifica. Il file di formato è basato sull'opzione specificata relativa al tipo di dati (-n, -c, -w, o -N) e sui delimitatori della tabella o della vista.

Quando si esegue l'importazione in blocco in una tabella di SQL Server o si esportano in blocco dati da una tabella, è possibile usare un file di formato come sistema flessibile per la scrittura di file di dati. I file di formato richiedono nessuna modifica o modifiche minime per la conformità con altri file di dati da un altro prodotto software.

Limiti

La versione dell'utilità bcp (bcp.exe) usata per leggere un file di formato deve essere uguale o successiva alla versione usata per creare il file di formato. Ad esempio, bcp di SQL Server 2016 (13.x) può leggere un file di formato versione 12.0 generato da bcp di SQL Server 2014 (12.x), ma bcp di SQL Server 2014 (12.x) non può leggere un file di formato versione 13.0 generato da bcp di SQL Server 2016 (13.x).

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.

Creare file di formato

SQL Server supporta due tipi di file di formato, ovvero non XML e XML. Il formato non XML è il formato originale supportato dalle versioni precedenti di SQL Server.

In generale, i file di formato XML e non XML sono intercambiabili. È tuttavia consigliabile utilizzare la sintassi XML per i file di formato, in quanto questo tipo di file offre numerosi vantaggi rispetto ai file di formato non XML.

File di formato XML

Per l'articolo è necessario il database di esempio AdventureWorks2022, che è possibile scaricare dalla pagina iniziale del sito relativo a progetti della community ed esempi per Microsoft SQL Server. Adventure Works Cycles è un'azienda manifatturiera fittizia usata per esemplificare concetti e scenari relativi ai database.

Creare un file di formato XML

Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare nul anziché un percorso del file di dati. L'opzione format richiede sempre l'opzione -f. Inoltre, per creare un file di formato XML è necessario anche specificare l'opzione -x, ad esempio bcp <table_or_view> format nul -f <format_file_name> -x.

Per distinguere un file di formato XML, è consigliabile utilizzare .xml come estensione di file, ad esempio MyTable.xml.

Per informazioni sulla struttura e sui campi dei file di formato XML, vedere File in formato XML (SQL Server).

Esempi

Questa sezione contiene gli esempi seguenti che illustrano come usare i comandi bcp per creare un file di formato XML. La tabella HumanResources.Department contiene quattro colonne, ovvero DepartmentID, Name, GroupNamee ModifiedDate.

R. Creare un file di formato XML per dati di tipo carattere

Nell'esempio seguente viene creato un file di formato XML, Department.xml, per la tabella HumanResources.Department . Nel file di formato vengono utilizzati formati di dati di tipo carattere e un carattere di terminazione del campo non predefinito (,). Il contenuto del file di formato generato viene visualizzato dopo il comando.

Per il comando bcp sono disponibili i qualificatori seguenti.

Qualificatori	Descrizione
format nul -x -f <format_file>	Specifica il file di formato XML.
-c	Specifica i dati di tipo carattere.
-t,	Specifica la virgola (,) come carattere di terminazione del campo. Nota: se il file di dati usa il carattere di terminazione del campo predefinito (\t), l'opzione -t non è necessaria.
-T	Specifica che l'utilità bcp si connette a SQL Server con una connessione trusted che usa la sicurezza integrata. Se non si specifica -T, bisogna specificare -U e -P per eseguire correttamente l'accesso.

Al prompt dei comandi di Windows digitare il comando bcp seguente:

bcp AdventureWorks2022.HumanResources.Department format nul -c -x -f Department-c.xml -t, -T

Il file di formato generato, Department-c.xml, contiene gli elementi XML seguenti:

<?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="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Per informazioni sulla sintassi di questo file di formato, vedere File in formato XML (SQL Server). Per informazioni sui dati di tipo carattere, vedere Utilizzare il formato carattere per l'importazione o l'esportazione di dati (SQL Server).

B. Creare un file di formato XML per dati nativi

Nell'esempio seguente viene creato un file di formato XML, Department-n.xml, per la tabella HumanResources.Department . Nel file di formato vengono utilizzati tipi di dati nativi. Il contenuto del file di formato generato viene visualizzato dopo il comando.

Per il comando bcp sono disponibili i qualificatori seguenti.

Qualificatori	Descrizione
format nul -x -f <format_file>	Specifica il file di formato XML.
-n	Specifica i tipi di dati nativi.
-T	Specifica che l'utilità bcp si connette a SQL Server con una connessione trusted che usa la sicurezza integrata. Se non si specifica -T, bisogna specificare -U e -P per eseguire correttamente l'accesso.

Al prompt dei comandi di Windows digitare il comando bcp seguente:

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

Il file di formato generato, Department-n.xml, contiene gli elementi XML seguenti:

<?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="NativeFixed" LENGTH="2"/>
  <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="NativeFixed" LENGTH="8"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
</ROW>
</BCPFORMAT>

Per informazioni sulla sintassi di questo file di formato, vedere File in formato XML (SQL Server). Per informazioni su come usare i dati nativi, vedere Utilizzare il formato nativo per importare o esportare dati (SQL Server).

File di formato non XML

Per l'articolo è necessario il database di esempio AdventureWorks2022, che è possibile scaricare dalla pagina iniziale del sito relativo a progetti della community ed esempi per Microsoft SQL Server. Adventure Works Cycles è un'azienda manifatturiera fittizia usata per esemplificare concetti e scenari relativi ai database.

Creare un file di formato non XML

Per usare un comando bcp per creare un file di formato, specificare l'argomento format e usare nul anziché un percorso del file di dati. L'opzione format richiede anche l'opzione -f, come nell'esempio seguente: bcp <table_or_view> format nul -f <format_file_name>.

Per distinguere un file di formato non XML, è consigliabile utilizzare .fmt come estensione di file, ad esempio MyTable.fmt.

Per informazioni sulla struttura e sui campi dei file di formato non XML, vedere Utilizzare file di formato non XML (SQL Server).

Esempi

Questa sezione contiene gli esempi seguenti che illustrano come usare i comandi bcp per creare un file di formato non XML. La tabella HumanResources.Department contiene quattro colonne, ovvero DepartmentID, Name, GroupNamee ModifiedDate.

R. Creare un file di formato non XML per dati nativi

Nell'esempio seguente viene creato un file di formato XML, Department-n.xml, per la tabella HumanResources.Department . Nel file di formato vengono utilizzati tipi di dati nativi. Il contenuto del file di formato generato viene visualizzato dopo il comando.

Per il comando bcp sono disponibili i qualificatori seguenti.

Qualificatori	Descrizione
format nul -f <format_file>	Specifica il file di formato non XML.
-n	Specifica i tipi di dati nativi.
-T	Specifica che l'utilità bcp si connette a SQL Server con una connessione trusted che usa la sicurezza integrata. Se non si specifica -T, bisogna specificare -U e -P per eseguire correttamente l'accesso.

Al prompt dei comandi di Windows digitare il comando bcp seguente:

bcp AdventureWorks2022.HumanResources.Department format nul -T -n -f Department-n.fmt

Il file di formato generato, Department-n.fmt, contiene le informazioni seguenti:

12.0
4
1  SQLSMALLINT   0       2       ""   1     DepartmentID         ""
2  SQLNCHAR      2       100     ""   2     Name                 SQL_Latin1_General_CP1_CI_AS
3  SQLNCHAR      2       100     ""   3     GroupName            SQL_Latin1_General_CP1_CI_AS
4  SQLDATETIME   0       8       ""   4     ModifiedDate         ""

Per altre informazioni, vedere Utilizzare file di formato non XML (SQL Server).

B. Creare un file di formato non XML per dati di tipo carattere

Nell'esempio seguente viene creato un file di formato XML, Department.fmt, per la tabella HumanResources.Department . Nel file di formato vengono utilizzati formati di dati di tipo carattere e un carattere di terminazione del campo non predefinito (,). Il contenuto del file di formato generato viene visualizzato dopo il comando.

Per il comando bcp sono disponibili i qualificatori seguenti.

Qualificatori	Descrizione
format nul -f <format_file>	Specifica un file di formato non XML.
-c	Specifica i dati di tipo carattere.
-T	Specifica che l'utilità bcp si connette a SQL Server con una connessione trusted che usa la sicurezza integrata. Se non si specifica -T, bisogna specificare -U e -P per eseguire correttamente l'accesso.

Al prompt dei comandi di Windows digitare il comando bcp seguente:

bcp AdventureWorks2022.HumanResources.Department format nul -c -f Department-c.fmt -T

Il file di formato generato, Department-c.fmt, contiene le informazioni seguenti:

12.0
4
1  SQLCHAR       0       7       "\t"     1     DepartmentID            ""
2  SQLCHAR       0       100     "\t"     2     Name                    SQL_Latin1_General_CP1_CI_AS
3  SQLCHAR       0       100     "\t"     3     GroupName               SQL_Latin1_General_CP1_CI_AS
4  SQLCHAR       0       24      "\r\n"   4     ModifiedDate            ""

Per altre informazioni, vedere Utilizzare file di formato non XML (SQL Server).

C. Creare un file di formato non XML per dati nativi Unicode

Per creare un file di formato non XML per i dati nativi Unicode della tabella HumanResources.Department, utilizzare il comando seguente:

bcp AdventureWorks2022.HumanResources.Department format nul -T -N -f Department-n.fmt

Per altre informazioni sull'uso dei dati di tipo carattere Unicode, vedere Utilizzare il formato Unicode nativo per importare o esportare dati (SQL Server).

D. Creare un file di formato non XML per dati di tipo carattere Unicode

Per creare un file di formato non XML per i dati di tipo carattere Unicode della tabella HumanResources.Department che utilizza i caratteri di terminazione predefiniti, utilizzare il comando seguente:

bcp AdventureWorks2022.HumanResources.Department format nul -T -w -f Department-w.fmt

Per altre informazioni sull'uso dei dati di tipo carattere Unicode, vedere Utilizzo del formato carattere per l'importazione o l'esportazione di dati (SQL Server).

F. Usare un file di formato con l'opzione della tabella codici

Se si crea un file di formato con il comando bcp (ovvero usando bcp format), le informazioni sulle regole di confronto o sulla tabella codici verranno scritte nel file di formato.

Il seguente file di formato di esempio per una tabella con 5 colonne include le regole di confronto.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0          Cyrillic_General_CS_AS
2  SQLCHAR         0       0       "\t"             2     c_1          Cyrillic_General_CS_AS
3  SQLCHAR         0       3000    "\t"             3     c_2          Cyrillic_General_CS_AS
4  SQLCHAR         0       5       "\t"             4     c_3          ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4          ""

Se si prova a importare i dati in SQL Server usando bcp in -c -C65001 -f format_file ..." o "BULK INSERT/OPENROWSET ... FORMATFILE='format_file' CODEPAGE=65001 ...", le informazioni sulle regole di confronto o sulla tabella codici avranno la priorità rispetto all'opzione 65001.

Di conseguenza, se si genera un file di formato, è necessario eliminare manualmente le informazioni sulle regole di confronto dal file di formato generato prima di iniziare a reimportare i dati in SQL Server.

Il seguente esempio mostra il file di formato senza le informazioni sulle regole di confronto.

13.0
5
1  SQLCHAR         0       0       "\t"             1     c_0              ""
2  SQLCHAR         0       0       "\t"             2     c_1              ""
3  SQLCHAR         0       3000    "\t"             3     c_2              ""
4  SQLCHAR         0       5       "\t"             4     c_3              ""
5  SQLCHAR         0       41      "!!!\r\r\n"      5     c_4              ""

Mappare i campi dati alle colonne della tabella

Un file di formato creato da bcpdescrive tutte le colonne della tabella in ordine. È possibile modificare un file di formato per spostare o omettere righe della tabella. É possibile personalizzare un file di formato in base a un file di dati i cui campi non eseguono il mapping direttamente alle colonne della tabella. Per altre informazioni, vedere gli articoli seguenti:

• Usare un file di formato per ignorare una colonna di una tabella (SQL Server)
• Usare un file di formato per escludere un campo dati (SQL Server)
• Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)

Contenuto correlato

• Utilità bcp
• Usare file di formato non XML (SQL Server)
• File in formato XML (SQL Server)